Running Access Queries From Excel Using VBA

Share this

October 6, 2013

Running Access Queries From Excel Using VBA


Introduction


Two weeks ago, I published a VBA code for retrieving values from DBF (database) files. Some days after that post, I received some e-mail requests about doing the same, but for Access databases. More precisely, two readers (Josh and Maria) asked me to write two different VBA codes for running Access queries directly from Excel. I decided to “answer” both requests by writing this post since both are related to the same subject: Access queries.

In the next section, you will find two Excel VBA code snippets that show you how to retrieve data from an Access database. The idea behind both macros is the same:

  • Create and open a connection to the Access database.
  • Create a recordset that will contain the query results.
  • Create the necessary SQL select statement or set the query name.
  • Open the recordset.
  • If the recordset has data, write them into Excel.
  • Finally, close the recordset and the connection.

Both codes have many similarities and share a lot of code lines. Their main difference, however, is in the query part. The first code (CreateAndRunQuery) creates the query on the fly, while the second one (RunExistingQuery) uses an existing database query.

 


VBA code for running access queries


Both macros below use the Sample.accdb database, located in the same folder as the workbook. First, the CreateAndRunQuery macro is presented, which runs a query on the Customers table of the database, retrieving information (names, addresses, etc.) from all the customers from Canada.

Create and run Access queries on the fly

Option Explicit

Sub CreateAndRunQuery()
    
    '------------------------------------------------------------------------------------------
    'This macro opens the Sample.accdb database, creates and runs a SQL query (filtering
    'all the customers from Canada). Then, it copies selected fields back in the Excel sheet.
    'The code uses late binding, so no reference to an external library is required.
    
    'Written By:    Christos Samaras
    'Date:          05/10/2013
    'Last Updated:  29/11/2014
    'E-mail:        [email protected]
    'Site:          https://www.myengineeringworld.net
    '------------------------------------------------------------------------------------------

    'Declaring the necessary variables.
    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strTable    As String
    Dim SQL         As String
    Dim i           As Integer
            
    'Disable screen flickering.
    Application.ScreenUpdating = False
    
    'Specify the file path of the accdb file. You can also use the full path of the file like:
    'AccessFile = "C:\Users\Christos\Desktop\Sample.accdb"
    AccessFile = ThisWorkbook.Path & "\" & "Sample.accdb"
    
    'Set the name of the table you want to retrieve the data.
    strTable = "Customers"
    
    On Error Resume Next
    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")
    'Check if the object was created.
    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Open the connection.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile
    
    'Create the SQL statement to retrieve the data from the table.
    'Get the necessary information (first name etc.) for all the Canadian customers.
    SQL = "SELECT FirstName, LastName, Address, City, Phone FROM " & strTable & " WHERE COUNTRY='Canada'"
    
    On Error Resume Next
    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.Recordset")
    'Check if the object was created.
    If Err.Number <> 0 Then
        'Error! Release the objects and exit.
        Set rs = Nothing
        Set con = Nothing
        'Display an error message to the user.
        MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
        Exit Sub
    End If
    On Error GoTo 0
         
    'Set thee cursor location.
    rs.CursorLocation = 3 'adUseClient on early  binding
    rs.CursorType = 1 'adOpenKeyset on early  binding
    
    'Open the recordset.
    rs.Open SQL, con
    
    'Check if the recordset is empty.
    If rs.EOF And rs.BOF Then
        'Close the recordset and the connection.
        rs.Close
        con.Close
        'Release the objects.
        Set rs = Nothing
        Set con = Nothing
        'Enable the screen.
        Application.ScreenUpdating = True
        'In case of an empty recordset display an error.
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If
    
    'Copy the recordset headers.
    For i = 0 To rs.Fields.Count - 1
        Sheets("New Query").Cells(1, i + 1) = rs.Fields(i).Name
    Next i
    
    'Write the query values in the sheet.
    Sheets("New Query").Range("A2").CopyFromRecordset rs
    
    'Close the recordset and the connection.
    rs.Close
    con.Close
    
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    
    'Adjust the columns' width.
    Sheets("New Query").Columns("A:E").AutoFit
    
    'Enable the screen.
    Application.ScreenUpdating = True

    'Inform the user that the macro was executed successfully.
    MsgBox "The Canadian customers were successfully retrieved from the '" & strTable & "' table!", vbInformation, "Done"

End Sub 

Here is the RunExistingQuery macro, which runs the existing qrRegions query. The particular query counts the number of customers from each region on the Customers table.

Run existing Access queries

Option Explicit

Sub RunExistingQuery()
    
    '------------------------------------------------------------------------------------
    'This macro opens the Sample.accdb database and runs the (existing) qrRegions query
    '(counting the number of customers from each region, based on table Customers).
    'Then, it copies all the query results back in the Excel sheet.
    'The code uses late binding, so no reference to an external library is required.
    
    'Written By:    Christos Samaras
    'Date:          05/10/2013
    'Last Updated:  29/11/2014
    'E-mail:        [email protected]
    'Site:          https://www.myengineeringworld.net
    '------------------------------------------------------------------------------------

    'Declaring the necessary variables.
    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strQuery    As String
    Dim i           As Integer
            
    'Disable screen flickering.
    Application.ScreenUpdating = False
    
    'Specify the file path of the accdb file. You can also use the full path of the file like:
    'AccessFile = "C:\Users\Christos\Desktop\Sample.accdb"
    AccessFile = ThisWorkbook.Path & "\" & "Sample.accdb"
    
    'Set the name of the query you want to run and retrieve the data.
    strQuery = "qrRegions"
    
    On Error Resume Next
    'Create the ADODB connection object.
    Set con = CreateObject("ADODB.connection")
    'Check if the object was created.
    If Err.Number <> 0 Then
        MsgBox "Connection was not created!", vbCritical, "Connection Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Open the connection.
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile
    
    On Error Resume Next
    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.Recordset")
    'Check if the object was created.
    If Err.Number <> 0 Then
        'Error! Release the objects and exit.
        Set rs = Nothing
        Set con = Nothing
        'Display an error message to the user.
        MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
        Exit Sub
    End If
    On Error GoTo 0
         
    'Set thee cursor location.
    rs.CursorLocation = 3 'adUseClient on early  binding
    rs.CursorType = 1 'adOpenKeyset on early  binding
    
    'Open the recordset.
    rs.Open strQuery, con
    
    'Check if the recordset is empty.
    If rs.EOF And rs.BOF Then
        'Close the recordset and the connection.
        rs.Close
        con.Close
        'Release the objects.
        Set rs = Nothing
        Set con = Nothing
        'Enable the screen.
        Application.ScreenUpdating = True
        'In case of an empty recordset display an error.
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If
    
    'Copy the recordset headers.
    For i = 0 To rs.Fields.Count - 1
        Sheets("Existing Access Query").Cells(1, i + 1) = rs.Fields(i).Name
    Next i
    
    'Write the query values in the sheet.
     Sheets("Existing Access Query").Range("A2").CopyFromRecordset rs
    
    'Close the recordset and the connection.
    rs.Close
    con.Close
    
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    
    'Adjust the columns' width.
    Columns("A:B").AutoFit
    
    'Enable the screen.
    Application.ScreenUpdating = True

    'Inform the user that the macro was executed successfully.
    MsgBox "All data were  successfully retrieved from the '" & strQuery & "' query!", vbInformation, "Done"

End Sub 

Both codes use late binding, so no reference to an external library is required. The Access queries that were used here were relatively simple to demonstrate the macros. Of course, you can use more complicated ones.

 


Downloads


Download

The zip file contains an Excel workbook containing the two macros presented above and a sample Access database. The workbook can be opened with Excel 2007 or newer.

 


Read also 


Read DBF Files Using VBA
Export A Large Access Table/Query To Excel
Add Records Into Existing Access Table From Excel Using VBA

Page last modified: May 16, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

  • Thank you for your kind words, Tim!

    VBA has not changed a lot during the last decade.
    So, although these routines were written almost 8 years ago, they are still valid.
    Of course, the same is true for most of the code snippets that you will find on this site.

    Best Regards,
    Christos

  • Thank you. Even years later your code is still solving problems.😊

  • Excellent site keep it up

  • Hi, Vlad,

    In that case, you will loop through each row of the recordset and add the data to the collection.
    Here is a quick example:

       'Define the collection.
        Dim myCollection As New Collection
    
        'Go to the first record.
        rs.MoveFirst
        Do Until rs.EOF = True
            'Add the first column in the collection.
            myCollection.Add rs(0).Value
            'Move to the next record.
            rs.MoveNext
        Loop

    Best Regards,
    Christos

  • Vlad Valentine says:

    Hello, how would you put the data into a collection instead of a sheet?

  • Hi, Sharon,

    To use an Access UDF, the code snippets in the post will not work.
    You will have to do something like this:

    Const cstrDbFile As String = "C:\share\Access\Database2.accdb"
    Dim objAccess As Object
    Dim rs As Object
    Dim ws As Worksheet
    Dim strSelect As String
    
    Set objAccess = CreateObject("Access.Application")
    objAccess.Visible = True ' useful during testing '
    objAccess.OpenCurrentDatabase "C:\Users\Test\Database.accdb"
    strSelect = "SELECT ID, DummyAccessUDFFunction('a', 'b') FROM DummyTable;"
    Set rs = objAccess.CurrentDb.OpenRecordset(strSelect)
    If Not rs.EOF Then
        Set ws = ThisWorkbook.Sheets("Sheet1")
        ws.Range("A1").CopyFromRecordset rs
    End If
    rs.Close
    objAccess.Quit

    I hope it helps!

    Best Regards,
    Christos

  • Hi,
    I have an existing query in Access that uses a user-defined function (function in Access). When I tried querying from Excel? It will complain unable to find that user-defined function. I tried copying that function into Excel as well but still having the same problem. I have no issue running that query in Access. Any idea how I can get it to work? Thanks in advance!

  • Hi, Sheba,

    In which of the queries you are referring to?
    By the way, this is Excel, not Access code.

    Best Regards,
    Christos

  • Hi, Kevin,

    First of all, sorry for not responding to you on time, but in the last few weeks I had to move to a new house, so it was very difficult for me to follow along with the comments on this site.

    Regarding your question, as long as the user has this driver installed, the code should work:
    https://www.microsoft.com/en-us/download/details.aspx?id=13255

    Note that I haven’t tried this in a computer that has no Access.

    Best Regards,
    Christos

  • Thanks for your answer Christos,

    I’ll try it tonight.

    Best regards
    Conrad

  • How would you code this to read an Access database for users who don’t have the Access application on their machines?

  • Hi, Conrad,

    Yes, it is possible.
    Instead of CopyFromRecordset , it would be something like this (in the above code example):

    
        'Write the query values in the sheet.
         i = 2
        With Sheets("New Query")
            rs.MoveFirst
            Do Until rs.EOF = True
                .Cells(i, 1) = rs!FirstName 'A2
                .Cells(i + 3, 9) = rs!LastName 'I5
                'Move to the next record.
                rs.MoveNext
                i = i + 1
            Loop
        End With
    

    I hope it helps!

    Best Regards,
    Christos

  • Hello,

    Your code is great and helped me a lot, but i have a question: is it possible to write each query value into differents cells? For instance, instead of having all the values written in excel from Range(“A2”), could it be possible to have the first value in Range(“A2), the second in Range (“I5”), the next in Range(“M3”)?

    Thanks you for your answer

  • Hi,

    i am unable to run the query in access 2013

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >