[Excel VBA] Basic Code to Connect to Access Database (Using DAO)

Introduction

To connect to and manipulate an Access database (.accdb) directly from Excel VBA, it is common to use DAO (Data Access Objects).

This method establishes the connection required to reference tables/queries or write data to Access from VBA. This article introduces the basic VBA code to connect to an Access database using DAO.

Prerequisites

• Target Database: An Access file named project_data.accdb.
• Location: The database file is placed in the same folder as the Excel file running the macro.
• Environment: DAO 12.0 (DAO.DBEngine.120) must be installed (standard on most Windows systems with Office).

VBA Code: Connecting to Access and Checking Version

Sub ConnectToAccessDB()
    Dim daoEngine As Object
    Dim accessDB As Object

    ' Check DAO Version
    MsgBox CreateObject("DAO.DBEngine.120").Version, vbInformation, "DAO Version"

    ' Initialize Engine and Open Database
    Set daoEngine = CreateObject("DAO.DBEngine.120")
    Set accessDB = daoEngine.OpenDatabase(ThisWorkbook.Path & "\project_data.accdb")
    
    ' Connection Confirmation Message
    MsgBox "Connected to: " & accessDB.Name, vbInformation, "Connection Successful"

    ' Cleanup
    accessDB.Close
    Set accessDB = Nothing
    Set daoEngine = Nothing
End Sub

Execution Results

1. First Message Box: Displays the DAO version (e.g., 12.0).
2. Second Message Box: Displays the full path indicating success, e.g., Connected to: C:\Users\...\project_data.accdb.

Supplementary Information

Common Errors and Solutions

• Compile Error “Library not found”: This usually happens with Early Binding, but since we are using CreateObject (Late Binding), this is rare. If it occurs, ensure your Office installation includes Access components.
• Runtime Error “File not found”: Ensure project_data.accdb is actually in the same folder as your Excel file (ThisWorkbook.Path).
• Version not displayed: The DAO runtime might not be installed or could be corrupted.

Summary

Using the DAO object is a simple and reliable way to connect to an Access database from Excel VBA. Mastering this connection process is the essential first step before moving on to reading data, writing records, or executing queries.