Access������--
ËùÊô·ÖÀࣺ ADPÏà¹Ø ×÷Õߣº ÀîÑ°»¶ ¸üÐÂÈÕÆÚ£º2004-1-1 21:23:13 ÔĶÁ´ÎÊý£º1157

SQL SERVER»ù±¾¸ÅÄîѧϰӡÏó


ûѧSQL SERVER֮ǰ£¬ÎÒÔøÏë¹ý°Ñ×ÔÒÑÒÔÇ°×öµÄһЩ¹ÜÀí³ÌÐòÉý¼¶³ÉADP£¬µ«Ò»¿´µ½Ê²Ã´´æ´¢¹ý³Ì¡¢ÊÓͼ¡¢´¥·¢Æ÷Ö®ÀàµÄ¶«Î÷¾ÍÍ·´ó£¬È»ºóһֱʹÓõIJéѯҲ²»ÖªµÀÔõô½¨£¬ÊµÔÚÊǺÜÍ·Í´¡£ÆäʵÕâЩ¶«Î÷²¢Ã»ÏëÏóÖеÄÄÑÒÔ½ÓÊܼ°ÄÑѧ£¬ÏÂÃæÎҾͰÑ×ÔÒѶÔÓÚ´æ´¢¹ý³Ì¡¢ÊÓͼ¡¢´¥·¢Æ÷»¹ÓÐÓαêµÄÀí½âÕûÀíÒ»ÏÂд³öÀ´£¬¾Íµ±ÊÇÎÂÏ°¹¦¿Î°É£¬ÒòΪ¹ý¼¸ÌìÒª¿¼ÊÔÁË£¬ÓÉÓÚ³õѧ£¬Óв»¶ÔµÄµØ·½£¬Çë¸÷λÅúÆÀÖ¸Õý¡£

ÊÓͼ£ºÊÓͼ¾ÍÊÇMDBÖеIJéѯ£¬ÄãÔÚmdbÖÐ×öµÄ²éѯ£¬ÔÚadpÖÐת³ÉÊÓͼ¾ÍÐÐÁË£¬Ó÷¨ÊÇÒ»ÑùµÄ£¬¾ÍÊÇ°ÑËüµ±³É±íÀ´Óá£

 ¡ïÊÓͼÊÇÓɲéѯ¶¨ÒåµÄÐé±í¡£
 ¡ïÊÓͼÔÚÎïÀíÉϲ¢²»´æÔÚ£¬ËüÖ»ÊÇÊý¾Ý¿âÖÐÊý¾ÝÖµµÄ´æ´¢¼¯ºÏ¡£
 ¡ïÊÓͼÓë±íÊ®·ÖÏàËÆ£¬°üº¬Èô¸ÉÁкÍÐС£¿ÉÒÔ½øÐÐÑ¡Ôñ¡¢¸üкÍɾ³ý²Ù×÷¡£µ«¶ÔÊÓͼÐÞ¸ÄÓÐÏÞÖÆ¡£
 ¡ïʹÓÃÊÓͼµÄºÃ´¦ÔÚÓÚ£¬¿ÉÒÔ¿ØÖÆÓû§¿É²é¿´µÄÊý¾Ý£¬´Ó¶øÌṩÁËÊý¾ÝµÄ°²È«ÐÔ¡£

´æ´¢¹ý³Ì£ºµ¥¶ÀµÄSQLÓï¾äÏàÐÅ´ó¼Ò¶¼Óùý£¬¹ý³ÌÏàÐÅ´ó¼ÒÒ²¶¼Óùý£¬±ÈÈçÒ»¸ö°´Å¥µÄµ¥»÷ʼþ£¬Ê¼þÀïµÄÓï¾ä¾ÍÊÇÒ»¸ö¹ý³Ì£¬¿ÉÒÔÔÚ¹ý³ÌÀïÉùÃ÷±äÁ¿£¬ÒÔ¼°ÓÃÁ÷³Ì¿ØÖÆÓï¾ä¸Ä±ä³ÌÐòÓï¾äµÄÖ´ÐÐ˳Ðò¡£Èç¹ûµ¥¶ÀµÄSQLÓï¾ä¼°ÆÕͨµÄ¹ý³Ì´ó¼Ò¶¼»áÓã¬ÄÇ´æ´¢¹ý³ÌÒ²¾ÍûÎÊÌâÁË£¬ÒòΪ´æ´¢¹ý³ÌÊǵ¥Ìõ»ò¶àÌõSQLÓï¾äµÄ×éºÏ£¬²¢¿ÉÉùÃ÷±äÁ¿£¬¿É½ÓÊÕ¡¢´«³ö±äÁ¿£¨²ÎÊý£©£¬Ò²¿ÉÓÃÁ÷³Ì¿ØÖÆÓï¾ä¸Ä±äSQLÓï¾äµÄÖ´ÐÐ˳Ðò¡£

 ¡ï´æ´¢¹ý³ÌÌṩÁ˱ȸö±ðSQLÓï¾ä¸ü¶àµÄÓŵ㣺
 ¡ï¸ü¿ìËÙµØÖ´ÐУºµÚÒ»´ÎÖ´ÐÐʱ±àÒ룬ÒÔºó¿ÉÒÔÖ±½ÓÖ´ÐС£
 ¡ï±¾µØºÍÔ¶³ÌÖ´ÐС£
 ¡ïÄ£¿é»¯±à³Ì·½Ê½£¨Öظ´Ê¹Óã©¡£
 ¡ï°Ñ´óµÄ³ÌÐò·Ö³ÉСµÄ¿ÉÒÔ¶ÀÁ¢ÔË×÷µÄ´æ´¢¹ý³Ì£¬ÒÔºó¿ÉÒÔÔÚÐèÒªµÄµØ·½µ÷ÓÃ
 ¡ïÔöÇ¿µÄ¿É¿¿ÐÔ£º¿ÉÒÔÀûÓÃreturn·µ»ØÖµÀ´¼ì²éÖ´ÐеÄÇé¿ö
 ¡ïÒ»ÖÂÐÔ¡£°Ñ¸´ÔӵIJÙ×÷д³É´æ´¢¹ý³Ì£¬ÔÚÐèҪʱµ÷Ó㬿ÉÒÔ±ÜÃâÓû§²»Ð¡ÐĵIJÙ×÷´íÎ󡣶à´ÎʹÓö¼Ò»Ñù
 ¡ï°²È«ÐÔ¡£Ö»Òª¿ÉÒÔÖ´Ðд洢¹ý³Ì£¬Ôò¿ÉÒÔͨ¹ý´æ´¢¹ý³Ì·ÃÎÊÎÞȨÏÞ·ÃÎʵÄÊý¾Ý¡£

´¥·¢Æ÷£º´¥·¢Æ÷¿ÉÒÔ˵ÊDZíµÄʼþ£¬Ëü¶Ô±íµÄijÀà²Ù×÷£¨²åÈë¡¢¸üлòɾ³ý²Ù×÷£©×ö³ö·´Ó¦¡£

´¥·¢Æ÷ÊÇÏ൱ÓÐÓõĶ«Î÷£¬ÎÒ¾ÙÒ»¸öÓ¦ÓõÄÀý×Ó¡£
²Ö¿â¹ÜÀíϵͳÖеÄÁ½¸ö±í£ºÈë¿âÃ÷ϸ±í¡¢ÎïÁÏ±í£¬Ã¿µ±Èë¿âÃ÷ϸ±íÐÂÔöÒ»Ìõ¼Ç¼£¬ÎïÁϱíÖеġ°µ±Ç°¿â´æ¡±×Ö¶ÎÒ²Òª¼ÓÉÏÏàÓ¦µÄÊýÁ¿£¬Õâ¸öʱºò¾Í¿ÉÓô¥·¢Æ÷À´Íê³É£¬Ã¿µ±²åÈë¼Ç¼ʱ£¬¾Í´¥·¢Ò»¸ö¹ý³ÌÀ´Íê³É¸ü¸Ä¡°µ±Ç°¿â´æ¡±×ֶεÄÈÎÎñ¡£
 ¡ï´¥·¢Æ÷ÊÇÔÚ¶Ô±íÖ´ÐвåÈë¡¢¸üлòɾ³ý²Ù×÷ʱ£¬×÷ΪÏìÓ¦¶øÖ´ÐеĹý³Ì¡£
 ¡ïµ±Ö´ÐÐÉÏÊöÈκÎÒ»Ïî²Ù×÷ʱ£¬¾Í»á×Ô¶¯Æô¶¯´¥·¢Æ÷¡£
 ¡ï´¥·¢Æ÷ͨ³£ÓÃÓÚÇ¿ÖÆÖ´ÐÐÒµÎñ¹æÔò¡£´Ó¶ø±£Ö¤Êý¾ÝµÄÍêÕûÐÔºÍÒ»ÖÂÐÔ¡£
 ¡ïӦʹ´¥·¢Æ÷ºÍ±íµÄINSERT¡¢UPDATE»òDELETE²Ù×÷Ïà¹ØÁª¡£µ±Íê³É²Ù×÷Óï¾äʱ£¬×Ô¶¯Ö´Ðд¥·¢Æ÷¡£
 ¡ï¸ü¸ÄÊý¾Ýʱ¿Éµ÷Óô¥·¢Æ÷£¬¸ù¾Ý´¥·¢Æ÷µÄÀàÐ;ö¶¨£º
 ÔÊÐí»ò½ûÖ¹¸ü¸ÄÊý¾Ý
 µ÷ÓÃÒµÎñ¹æÔò£¨±£Ö¤Êý¾ÝÓÐЧµÄ¹æÔò£©
 ÔÚÕû¸öÊý¾Ý¿âÄÚ¼¶Áª¸üкͼ¶ÁªÉ¾³ý

Óα꣺

DAO»òADOµÄ¼Ç¼¼¯´ó¼Ò¶¼Óùý°É£¬ÖØÎÂÒ»ÏÂʹÓüǼ¼¯µÄ²½Ö裺
1¡¢ÉùÃ÷¼Ç¼¼¯£¬²¢´Ó¼Ç¼ԴÖлñÈ¡¼Ç¼¼¯
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Set cn = CurrentProject.Connection
sql = "select * from ±í1"

2¡¢´ò¿ª¼Ç¼¼¯
rs.Open sql, cn, adOpenKeyset, adLockPessimistic, 1

3¡¢ÔڼǼ¼¯ÖжÁÈ¡¼Ç¼
Do While Not rs.EOF
Debug.Print rs.Fields(1)
rs.MoveNext
Loop

4¡¢¹Ø±Õ¼Ç¼¼¯¼°Á¬½Ó
rs.Close
cn.Close

5¡¢ÊÍ·ÅÁ¬½Ó
Set cn = Nothing

ÏÂÃæÊÇÓαêµÄʹÓÃʾÀý£º
1¡¢ÉùÃ÷Óα꣬²¢»ñÈ¡ÓαêËùÐè¼Ç¼¼¯
declare OrderQty cursor static
for select orderno,sum(Qty) from OrderDetails group by orderNO
declare @v_qty smallint,@v_orderno varchar(10)

2¡¢´ò¿ªÓαê
open OrderQty

3¡¢ÔÚÓαêÖжÁÈ¡¼Ç¼
fetch next from OrderQty into @v_orderno,@v_qty
while (@@fetch_status=0)
begin
if @v_qty<25
print 'Order number'+@v_orderno+' has a low order quantity'
else
print 'Order number'+@v_orderno+' has a low order quantity'
fetch next from OrderQty into @v_orderno,@v_qty
end

4¡¢¹Ø±ÕÓαê
close OrderQty

5¡¢ÊÍ·ÅÓαêËùµãÓÃ×ÊÔ´
deallocate OrderQty

¿´ÁËÉÏÃæµÄ¶Ô±ÈÖ®ºóÊDz»ÊǾõµÃÓαêºÜÏóDAO»òADOÖеļǼ¼¯ÄØ£¬Ö»ÊÇijЩÓï·¨²»Ì«Ò»Ñù¶øÒÑ£¬Èç¹ûÄã»áʹÓüǼ¼¯£¬Ö»Ðè×î¶à°ëÌìʱ¼ä¾Í¿Éѧ»áʹÓÃÓαꡣ

 ¡ïÓαê(Cursor)¿ÉÒÔ¿´³ÉÊÇÒ»¸öÓÃÀ´±£´æ¡°¼Ç¼¼¯¡±µÄ¶ÔÏ󣬼Ǽ¿ÉÒÔÖðÌõ´ÓÓαêÖжÁÈ¡¡£
 ¡ïSQL ServerÖ§³ÖÔÚ·þÎñÆ÷É϶¨ÒåºÍ´æ´¢Óα꣬ÕâЩÓαê³ÆΪ·þÎñÆ÷¶ËÓαꡣ
 ¡ï·þÎñÆ÷ά»¤×Ź¹³ÉÓαêµÄÕû¸ö¼Ç¼¼¯¡£Óαêͨ³£´Ó´æ´¢¹ý³Ì»ò´¥·¢Æ÷Öе÷Óá£
 ¡ïÓαêÁ½ÖÖÌØÐÔ£º´¦ÀíÌØÐԺ͹¦ÄÜÌØÐÔ¡£




--------------------------------------------------------------------------------
Ïà¹ØÎÄÕÂ

µ÷ÓÃoutlookÉú³É²¢·¢ËÍÓʼþµÄ´úÂë 2004-1-30 10:32:07
Ò»×éSQL ServerÉí·ÝÑéÖ¤¹ÜÀí×Ó³ÌÐò 2003-12-31 16:29:54
Access Êý¾Ý¿âºÍ Access ÏîÄ¿Ö®¼äµÄÊý¾ÝÀàÐ͵ıȽϻòÓ³Éä 2003-12-26 14:20:19
´óÐÍÊý¾Ý¿âÉè¼ÆÔ­Ôò 2003-12-15 12:49:18
ERwin--¼ò»¯µÄÊý¾Ý¿âÉè¼Æ¹¤¾ß 2003-12-15 12:41:40
ERwinÈí¼þ¼ò½é 2003-12-15 12:39:55
Á˽â Microsoft Access °²È«ÐÔ 2003-11-13 11:21:08
±ÜÃâAccessºÍSQL ServerµÄ¿ÕÖµ³åÍ» 2003-10-17 20:03:19
SQL SERVER ÓëACCESS¡¢EXCELµÄÊý¾Ýת»» 2003-10-17 19:12:00
MS Jet SQL for Access 2000Öм¶Æª (II) 2003-10-17 18:51:01
MS Jet SQL for Access 2000Öм¶Æª (I) 2003-10-17 18:47:38
ʹÓÃADOÁ¬½Óµ½·À»ðǽºóµÄSQL Server 2003-10-17 18:43:57
ADOÁ¬½ÓÊý¾Ý¿â×Ö·û´®´óÈ« 2003-10-17 18:40:47
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨¶þ£© 2003-10-14 8:45:33
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Èý£© 2003-10-14 8:44:20
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Ò»£© 2003-10-14 8:43:26
AccessÆô¶¯ÃüÁîÐÐÑ¡Ïî´óÈ« 2003-10-10 10:02:00
Êý¾Ý¿âÓ¦ÓóÌÐòÐè×¢ÒâµÄÎÊÌâ 2003-10-6 11:11:06


ϵͳÓÅ»¯
¿Ø¼þʹÓÃ
Êý¾Ý¿âÉè¼Æ
Êý¾Ý¿âÁ¬½Ó
ϵͳ°²È«
OLE×Ô¶¯»¯
³£¼ûÎÊÌâ
ʵÓôúÂë
ÊôÐÔÏê½â
ÍøÂçÏà¹Ø
ʵÓÃAPI
¾­Ñé·ÖÏí
¾«Ñ¡½Ì³Ì
×Ö·û´¦Àí
ADPÏà¹Ø



ÎÄÕÂËÑË÷



ÖÆ×÷ά»¤£ºÀîÑ°»¶     Mail:[email protected]

¹ØÓÚ±¾õ¾ -- ÍøÕ¾·þÎñ -- °æȨÌõ¿î -- ÁªÏµ·½·¨ -- ÍøÕ¾°ïÖú
Access°®ºÃÕß°æȨËùÓÐ Copyright 2003-2005 All Rights Reserved δ¾­Ðí¿É²»µÃµÁÁ´