[Excel VBA] How to Import Access Tables: A Simple Guide for Beginners

I will introduce how to easily read table data saved in an Access database (.accdb format) using Excel VBA. This article explains how to use DAO (Data Access Objects) to retrieve Access information using only VBA code.

The VBA Code

Below is an example that reads data from a table named tbl_staff inside an Access file and outputs it starting from cell B2 in Excel.

Sub ImportAccessData()
    Dim daoEngine As Object
    Dim accessDB As Object
    Dim recordSet As Object
    Dim dbPath As String

    ' Specify the path to the Access file
    dbPath = ThisWorkbook.Path & "\sample_database.accdb"

    ' Initialize DAO engine and connect to the database
    Set daoEngine = CreateObject("DAO.DBEngine.120")
    Set accessDB = daoEngine.OpenDatabase(dbPath)

    ' Get recordset from the table
    Set recordSet = accessDB.OpenRecordset("tbl_staff")

    ' Paste data starting at cell B2
    Sheet1.Range("B2").CopyFromRecordset recordSet

    ' Close connections
    recordSet.Close
    accessDB.Close
End Sub

Key Points of the Code

1. CreateObject("DAO.DBEngine.120"): This defines the connection to Access using DAO 12.0.
2. OpenDatabase: Opens the database. In this example, ThisWorkbook.Path is used to dynamically set the path to the same folder as the Excel file.
3. OpenRecordset("tbl_staff"): Opens the specific table you want to read.
4. CopyFromRecordset: This method allows you to paste the entire dataset into cells at once.
5. Close: Always close the connection at the end to ensure the process finishes correctly.

Important Notes

• No References Needed: While you often need to add “Microsoft DAO 3.6 Object Library” or “Microsoft Office Access database engine Object Library” in the VBA References settings, using CreateObject (as shown above) allows the code to run without manual reference settings.
• File Path: If the location of your Access file changes, be sure to update the dbPath variable.

Applications

You can adapt the code introduced in this article for various uses by changing the Access table name, the destination cell, or the destination sheet. For example, you can create a macro that reads different tables and distributes them into separate Excel sheets.

Summary

By reading data stored in Access using Excel VBA, you can automate reporting and improve the efficiency of data aggregation tasks. Mastering the integration between Excel and Access will significantly accelerate your daily office automation. Please make use of this sample code!