How To – Connect Excel To Access – Database Using VBA

How to Connect Access Database in Excel Macros?

Updating Excel Spreadsheet from Access Database using this step by step Excel VBA Access Macro code is just that simple. We are going to use a ADODB connection in this sample code.

Just copy paste this Excel VBA Access MDB conenction code to your VBA Project.

Excel To Access Connection – Simplest Code

Just change these two things in the code. It is enough for this to work better.

1. Database path – sDBPath : This should have the exact folder path & MDB Access file name that is being accessed with this code.
2. Query String – sQuery : The SQL query in this variable should match the Database Table & the fields exactly as how it is defined in the MDB database.

Once these two fields are edited, then this code will perfectly fine in a Excel VB Editor.

'--------------------------------------------------------------------------------
'Code by author@officetricks.com (or) kumarapush777 (Fiverr)
'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Sub VBA_Connect_To_MDB_ACCESS()

    'Make Reference to Microsoft AxticX Data Objects Library
    Dim dbConn As ADODB.Connection, dbRecSet As ADODB.Recordset
    Dim sConnString As String, sQuery As String
    Dim sDBPath As String
    
    'Define MDB ACCESS file path
    sDBPath = "D:\OfficeTricks\SalesOrders.accdb"
    
    'SQL Query String
    sQuery = "SELECT CustomerID, CustFirstName , CustLastName from Customers;"
    
    'ADODB Conenction String to initiate connection with MDB ACCESS
    sConnString = "Provider=Microsoft.ace.OLEDB.12.0; Data Source=" & sDBPath & ";"
    
    'Open Connection
    Set dbConn = New ADODB.Connection
    dbConn.Open ConnectionString:=sConnString
    
    'Execute SQL Query & Get Records matching the query to recordset
    Set dbRecSet = New ADODB.Recordset
    dbRecSet.Open Source:=sQuery, ActiveConnection:=dbConn
    
    'If Query Returned Values, Read them one by one
    If (dbRecSet.RecordCount <> 0) Then
        Do While Not dbRecSet.EOF
            MsgBox dbRecSet.Fields(1).Value
            dbRecSet.MoveNext
        Loop
    End If
    
    'Close Connection & RecordSet
    Set dbRecSet = Nothing
    dbConn.Close
    Set dbConn = Nothing
End Sub

Make sure that the MDB database Table has correct field names as specified in the query. Also it has enough data.

Note: Before executing this code, from your VB Edifor go to Tools in the menu -> References & add a reference to “Microsoft ActiveX Data Objects Library”. This is to make sure that ADODB object can be created from within the VBA Macro code.

The loop after that is present after the recordset.open command will get records from the table one by one, till end of the table. Make sure the replace the msgbox command with some assignment. If not you will end up in giving too many ‘OK’ clicks for the message box that pops up for every record fetch.

Apart from recordset.Movenext, there are other commands available to move the cursor or current position to First or last record or to any desired point as well.

In the next tutorial, we will see how to query Access database & load them to a list box in userform.