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
 
        .UsedRange.Clear
 
        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()
 
    objConnection.Close
 
    Set objConnection = Nothing
 
End Sub