Read from Access Table into Excel

Vishesh's picture
Following is a very simple piece of code to import an Access Table into Excel.

- Run the procedure ReadFromAccess.

- Copy the complete code given below:

Option Explicit
'Goto Menu - Tools->References and add reference to Microsoft ActiveX Data _
 Objects 2.8 Library

Dim objConnection As ADODB.Connection
Sub ConnectToDatabase(strDBpath As String)
    Set objConnection = New ADODB.Connection
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & strDBpath
End Sub
Sub ReadFromAccess()
    Dim strDB               As String
    Dim rstTable            As ADODB.Recordset
    Dim strTable            As String
    Dim intFields           As Integer
'---------------------User Inputs-------------------------------

'Provide database path

    strDB = "C:\Users\\Program Files\Root\Sample\db_samples.mdb"
'Provide SQL Query or Table name from database

    strTable = "Employee"

    Call ConnectToDatabase(strDB)
    Set rstTable = New ADODB.Recordset
    rstTable.Open strTable, objConnection, adOpenKeyset, adLockOptimistic, adCmdTable
    With Sheet1
        For intFields = 0 To rstTable.Fields.Count - 1
            .Range("A1").Offset(, intFields).Value = rstTable.Fields(intFields).Name
        Next intFields
        .Range("A1").Resize(, rstTable.Fields.Count).Font.Bold = True
        .Range("A2").CopyFromRecordset rstTable
    End With
    Set rstTable = Nothing
    Call CloseDB
End Sub
Sub CloseDB()
    Set objConnection = Nothing
End Sub