Read Access Data Into an Excel Worksheet
The code below illustrates how to read an Access table from Excel. You must include the Microsoft DAO 3.6 Object Library into the Excel Application.
Program Code
' *********************************************************
' Read Access Data Into Excel
' Make Sure To Use The Microsoft DAO 3.6 Object Library
' *********************************************************
Sub MyButton_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Long
Dim strSQL As String
i = 0
strSQL = "SELECT City FROM tblCity"
Set dbs = DBEngine.OpenDatabase("C:\TempRich1\db1.mdb")
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF Then
MsgBox "No Data"
Exit Sub
End If
Do
i = i + 1
Cells(i, 1).Value = rst!City
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub