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

ADO°ÝÃD

ADO°ÝÃD

³Ìªñ¶}©l¾Ç²ß¨Ï¥ÎADO³sµ²Access¥\¯à, ¦b³o·Q½Ð±Ð¦U¦ì«e½ú¤@¨Ç°ÝÃD, ÁÂÁÂ~
1. ¦b¬õ¦r³¡¥÷, ¦pªG¨S¦³where.... ¥\¯à¬O¥¿±`ªº, ¥[¤Fwhere´N·|¥X²{2147217913 Automationªº¿ù»~
     ¤£ª¾¹D³o³¡¥÷¸Ó«ç»ò±ø§ï!?
2. CursorType:=adOpenDynamic, LockType:=adLockOptimistic ³o2¥y¦b¨ä¥LªºÀ³¥Î·|¦³¤£¦P¶Ü?(¥Ø«e¦³¬Ý¨ì¥t¤@ºØCursorType:=adOpenStatic)

Sub ADO()
Dim strDB As String, strSQL As String
Dim i As Long, n As Long, lFieldCount As Long
Dim rng As Range
Dim adoRecSet As New ADODB.Recordset
Dim connDB As New ADODB.Connection

strDB = "C:\Users\Color\Desktop\Database1.accdb"
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0; data source=" & strDB

Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("sheet1")
Set adoRecSet = New ADODB.Recordset
strSQL = "SELECT Code FROM Stock WHERE Price > 100"
adoRecSet.Open Source:=strSQL, ActiveConnection:=connDB, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
Set rng = ws.Range("A1")
lFieldCount = adoRecSet.Fields.Count

For i = 0 To lFieldCount - 1
rng.Offset(0, i).Value = adoRecSet.Fields(i).Name
Next i

rng.Offset(1, 0).CopyFromRecordset adoRecSet
Range(ws.Columns(1), ws.Columns(lFieldCount)).AutoFit
adoRecSet.Close
connDB.Close
Set adoRecSet = Nothing
Set connDB = Nothing
End Sub


ACCESS¸ê®Æ®w³¡¥÷¶È¦³3Äæ°µ´ú¸Õ
Code    Price    Volume
123        99        100
456        456      200
ªì¾ÇªÌcolor

¤w¸Ñ¨M¡A½Ð§R¤å¡AÁÂÁ¡I
ªì¾ÇªÌcolor

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD