ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA ¥Îªí®æ¤º¬YÄæ¦ì¸ê®Æ¬d¸ßACCESS¸ê®Æ

[µo°Ý] VBA ¥Îªí®æ¤º¬YÄæ¦ì¸ê®Æ¬d¸ßACCESS¸ê®Æ

¤Wºôª¦¹L«Ü¦h¤å¦ý¬O³£¨S¿ìªk¦¨¥\
¥i¥H½Ð¦U¦ì¤j¤jÀ°­Ó¦£¶Ü
¬d¸ßµ²ªG¦s¤J¦P¦C¤§«á

PS¤@¶}©l´N¥d¦í¤F...«¬§O 'ADODB.Connection' ¥¼©w¸q¡C= =ºô¸ô¤W¼gªº¤u¨ã-¤Þ¥Î¤]¤£¦æ....

¦^´_ 1# toxin

³o¦³¨â­Ó¤èªk¸Ñ¨M

1)    ¦bExcel ¢äBA ¨Ï¥Î¨ì¡@ADO¡@«e¥ý´O¤J¡@¢ÏDO Library
¨ä°µªk¬°¦bVBA½s¿è¾¹¤¤, ¨Ï¥Î ¤u¨ã¡Ö³]©w¤Þ¥Î¶µ¥Ø
³o¼Ë·|±a¥X¡@"³]©w¤Þ¥Î¶µ¥Ø"µøµ¡
©ó¨ä¤¤®Ö¨ú¡@"¢Ûicrosoft ActiveX Data Objects x.x Library"¿ï¶µ¡@¡]¿ï¾Ü³Ì·sª©¥»ªºADO)
¦p¦¹§Y¥i©óVBA¤¤¨ú¥ÎADOªº¨ç¦¡®w
   
2)    ¦pªG§A¨S¦³±N ¢ÏDO Library¡@´O¤J
¦b«Å§i®É§A´N¤£¯à«Å§iª«¥óÅܼƬ°ADODB .Connection  ( ex. Dim DB As ADODB.Connection)
¦Ó¥u¯à«Å§i¬°¤@¯ëª«¥óÅܼƠ (Dim DB As ¢Ýbject)
«Ø¥ß®É§A¤£¯à¨Ï¥Î¡@¡@Set DB = New   ADODB.Connection¡@³o¼Ëªº»yªk
¦Ó­n¥Î¡@¡@¡@DB = CreateObject("Adodb.Connection")
Alex

TOP

'¤Þ¥Î ¢Ûicrosoft ActiveX Data Objects 2.x Library
Dim Cnnl As New ADODB.Connection '§A¬O¤£¬O§Ñ¤Fnew
Cnnl.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=D:\TEST.mdb; "
­Y¬O§Ú¦^µª¡A¨Ï±zº¡·N¡A½Ð±zÅý§Úª¾¹D¡I                  
­Y¬O§Úªº¦^ÂСA±z¤´¦³¨ä¥L¨£¸Ñ¡A¤]½Ð±z¤£¶Þ«ü±Ð¡I

TOP

¤£¦n·N«ä
¦pªG§t±K½Xªº¸Ü­n¥[¤°»ò?

TOP

¦^´_ 4# toxin
°²¦p§A¥Îªº¬OAccess ªºdatabae
connection string À³¸Ó¬O¥H¤Uªº«¬¦¡
"Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\databasename.mdb;Uid=yourid;Pwd=yourpassword"
©Î¬O
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\databasename.mdb;Jet OLEDB:Engine Type=5;Uid=yourid;Pwd=yourpassword"   ³o¼Ë¤]¦æ
Alex

TOP

¦^´_ 5# alexliou
ok¤F
·PÁ¤j¤j
¥i¥H¥t¥~¸ß°Ý¤@¤U
­n¬d¸ß±Æ§Ç«á²Ä¤@µ§¸ê®Æ
ºô¸ô¤WÁ¿access¥i¥H¥Î¤@¯ësql«ü¥O
¦ý¬O¤U¤F
SELECT TOP 1 ¥ó¸¹, ³æ»ù, ¼t°Ó FROM ³æ»ù©ú²Ó WHERE ¥ó¸¹  = '00002E-A'  ORDER BY 1 DESC
¸ò
SELECT * ¥ó¸¹, ³æ»ù, ¼t°Ó FROM ³æ»ù©ú²Ó WHERE ¥ó¸¹  = '00002E-A'  ORDER BY 1 DESC
¤@¼Ë³£·|¥X²{3µ§±Æ§Ç¹Lªº¸ê®Æ= =

¥t¥~¬d¸ß±ø¥ó'00002E-A'¥i¥H¥Îrange¥N´À¶Ü?

TOP

1. Access ªº½T¤ä´©SQL»yªk
SELECT TOP 1 ¥ó¸¹, ³æ»ù, ¼t°Ó FROM ³æ»ù©ú²Ó WHERE ¥ó¸¹  = '00002E-A'  ORDER BY 1 DESC
¬Ý°_¨Ó¨S¦³¿ù À³¸Ó­nÅã¥Ü²Ä¤@µ§¸ê®Æ

2. ¬d¸ß±ø¥ó'00002E-A'¥i¥H¥Îrange¤ºªº­È¥N´À
Alex

TOP

¦^´_ 7# alexliou
·PÁÂalexliou¤j¤j
ÁÙ¬O¦³¥H¤U´X­Ó°ÝÃD
1.²z½×¤WÀ³¸Ó¬O¥u¦³¤@µ§¸ê®Æ¡A¦ý¬O¹ê»Ú¤W¥ó¸¹¬Û¦Pªº³£·|¥X²{= =
2.½Ð°Ý¤@¤U­n¦p¦ó¬d¸ß?³£¥¢±Ñ»¡= =
¦pªG¤è«Kªº¸Ü¥i¥HÀ°¤p§Ì¬Ý¤@¤UÀɮ׶Ü?
¦pªþ¥ó 0510.rar (16.88 KB)
¥ý¦b¦¹·PÁ¤j¤j

TOP

¦^´_ 8# toxin
1. ¦b»POrder by¦X¥Î®É, TOP ­zµü¤£·|¦b¬Ûµ¥­È¤¤°µ¿ï¾Ü¡C
¦b±zªº¨Ò¤l¸Ì, ¦³¤Tµ§¸ê®Æªº¥ó¸¹¬°00002E-A
Top­zµü¨Ã«D®Ú¾Ú¸ê®Æ¦b¸ê®Æ®wªº¶¶§Ç¨Ó°Ï¤À½Ö¥ý½Ö«á,
¦Ó¬O¨Ì¾ÚOrder By Äæ¦ìªº­È¨Ó°Ï¤À
©Ò¥H¥¦µLªk°Ï¤À³o¤Tµ§ªº¥ý«á¶¶§Ç, ©Ò¥H¬d¸ß·|¶Ç¦^ 3 µ§°O¿ý

2. ¶Ç¦^ªºrecordset¤¤¦³¤Tµ§¬ö¿ý, ¦pªG­n°w¹ï­Ó§O¬ö¿ý°µ³B²z
¥i¥H§Q¥Î°j°é°t¦XrecordsetªºMovenext¤èªk»PEOFÄݩʨӳB²z

3.±z¤]¥i¥H§ïÅÜOrder byªºÄæ¦ì, ex .§ï¬° Order by ¼t°Ó
¦pªG¦P¤@¥ó¸¹¦P¤@¼t°Ó¥u¦³¤@µ§¬ö¿ý
¨º´N·|¥u¶Ç¦^¤@µ§¬ö¿ý
¦pªG¦P¤@¥ó¸¹¦P¤@¼t°ÓÁÙ¬O¦³¦hµ§¬ö¿ý
¦ý»ù®æ³£¤£¦P
´N¥i¥H¦A¼W¥[¤@­Óorder byÄæ¦ì
¦p Order by ¼t°Ó, ³æ»ù desc
Alex

TOP

¦^´_ 9# alexliou

·PÁÂalexliou¤j¤j¦^ÂÐ
»¡©úªº«Ü²M·¡...¦ý¬O¨Ì¸ê®Æ¿é¤J±¡ªp¨ÓÁ¿...¦n¹³¤£¤Ó¯à°÷¾a¨ä¥L±ø¥ó°µ±Æ§Ç§ä¨ì©Ò»Ý­nªº¸ê®Æ
©Ò¥H¥u¦n¥þ³¡³£¥ý¼g¥X¨Ó¤F....­n¾ainputbox¨Ó¨M©w­n¨º¤@µ§¸ê®Æ...
¤£¹L¸I¨ì¤@¥ó©Ç¨Æ...§âSelect  top 1§ï¦¨Select  *
´N·|¸I¨ì¿ù»~= =...©Ò¥H¼È®É¥ý¤USelect  top 100(¦]¬Y¨Ç¥ó¸¹©ú©ú¦³¦n´Xµ§¸ê®Æ«o¥u¥X²{¤@µ§)

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD