標題: ADO問題 [打印本頁]

---

作者: color790    時間: 2015-1-27 22:46     標題: ADO問題

最近開始學習使用ADO連結Access功能, 在這想請教各位前輩一些問題, 謝謝~
1. 在紅字部份, 如果沒有where.... 功能是正常的, 加了where就會出現2147217913 Automation的錯誤
     不知道這部份該怎麼條改!?
2. CursorType:=adOpenDynamic, LockType:=adLockOptimistic 這2句在其他的應用會有不同嗎?(目前有看到另一種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資料庫部份僅有3欄做測試
Code    Price    Volume
123        99        100
456        456      200

---

作者: color790    時間: 2015-1-30 18:31

已解決，請刪文，謝謝！

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)