Excel VBA---使用EXCEL連接ACCESS資料庫

最近不知道在想什麼，每天都在沒認真上班...

========= 開心的分隔線 =========

使用EXCEL連接ACCESS資料庫

需要先引用 Microsoft ActiveX Date Objects X.X Library，下段程式碼才能成功執行

Dim Conn As New ADODB.Connection, connstr As String, rs As New ADODB.Recordset
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.mdb;Jet OLEDB:Database Password=123"
Conn.Open connstr

執行SQL指令

Dim sqlstr As String

sqlstr = "SELECT * FROM master AS a LEFT JOIN detail AS b ON a.id=b.id GROUP BY a.id, currency_note"
    
rs.Open sqlstr, Conn, 1, 3

讀取資料

If rs.EOF And rs.BOF Then
    MsgBox "沒有資料"
Else
    Dim j As Long
    j = 1
    Do Until rs.EOF
       For i = 0 To rs.Fields.Count - 1
          Worksheets("Sheet1").Cells(j, i + 1).Value = rs(i)
       Next i

       j = j + 1

       rs.MoveNext
    Loop
End If