利用Excel VBA查詢Access資料庫

Sub test()

    Dim DB As Object, RS As Object, Target As String, Sql As String, TestString As String
    Set DB = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")

    Target = "d:\excel\test.accdb"
    DB.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Target & ";"
    
'test1
    TestString = "Dark"
    Sql = "SELECT Item from CP_item WHERE Type='" & TestString & "'"
    MsgBox Sql
    RS.Open Sql, DB, 3, 3
    
'test2
    'TestString = "Dark"
    'Sql = "SELECT * from CP_item WHERE Type='" & TestString & "'"
    'MsgBox Sql
    'RS.Open Sql, DB, 3, 3

    Sheets("工作表1").Cells.Clear
    Sheets("工作表1").Cells(1, 1).CopyFromRecordset RS

    RS.Close
    DB.Close
    Set RS = Nothing
    Set DB = Nothing

End Sub

snare wrote:
Sub test()(恕刪)

ataru_chiu wrote:
感謝回覆，只不過用您的code試了之後還是一樣有問題耶

'... 
exit sub 
errorhandler:

'msgbox ... ... 
'debug.print ... ... 

On Error GoTo -1
Err.Clear

end sub 

snare wrote:
我這邊試是正常的請用...(恕刪)

snare wrote:
我這邊試是正常的請用...(恕刪)

strainny wrote:
目前作業系統用win11 搭配office2010,
使用原版本下載來用 不過卻會出現下錯誤,