How To – Connect Excel To Access – Database Using VBA

Kumarapush

Learn how to connect Access database to Excel using VBA macro code. Thus uses ADODB connections methods to connect & query MDB Access database.

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.

    Database path – sDBPath : This should have the exact folder path & MDB Access file name that is being accessed with this code.

    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 http://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.