Sunday, 6 October 2013



Running Access Queries From Excel Using VBA


Introduction



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

In the next paragraph you will find two VBA code samples that can be used from Excel in order to retrieve data from an Access database.The first one (CreateAndRunQuery), creates the SQL query, while the second one (RunExistingQuery) uses an existing Access query. The idea behind both codes is the same:
  • Create and open a connection with Access database.
  • Create a recordset that will contain the query data.
  • Create or set the required SQL query.
  • Open the recordset using query and connection.
  • Retrieve required data from the recordset.
  • Write data in the Excel workbook.
  • Close both recordset and connection.
Both codes have many similarities and share a lot of code lines. However, apart from the query part, the two codes differ in the way the data from the recordset are retrieved (after running the query). In the first one (CreateAndRunQuery), the code loops through the recordset and passes the selected values/fields into an array, and, then, writes the values in Excel. On the other hand, the second one (RunExistingQuery), copies/writes directly all the query values in the workbook. So, regardless of the SQL query, both ways can be used to retrieve data from the recordset. The first one is more appropriate for retrieving only specific fields, while the second one is better for retrieving the entire recordset.



VBA code



Both macros below use the Sample.accdb database which is located in the same folder with the workbook. First the CreateAndRunQuery macro is presented, which creates and runs an SQL query using the Customers table of the database in order to filter all the customers from Canada.

Option Explicit

Sub CreateAndRunQuery()
    
    '------------------------------------------------------------------------------------------
    'This macro opens the Sample.accdb database, creates and runs an 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 external library is required.
    
    'Written by:    Christos Samaras
    'Date:          05/10/2013
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://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 myValues()  As Variant
    Dim i           As Long
    Dim j           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 read data from table. Filter all the customers from Canada.
    SQL = "SELECT * 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 "Connection was not created!", vbCritical, "Connection 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
    
    'Redim the table that will contain the filtered data.
    ReDim myValues(rs.RecordCount, 5)
    
    'Loop through the recordset and pass the selected values to the array.
    'In this example only 5 fields are required (not the entire recordet).
    i = 1
    If Not (rs.EOF And rs.BOF) Then
        'Go to the first record.
        rs.MoveFirst
        Do Until rs.EOF = True
            myValues(i, 1) = rs!FirstName
            myValues(i, 2) = rs!LastName
            myValues(i, 3) = rs!Address
            myValues(i, 4) = rs!City
            myValues(i, 5) = rs!Phone
            'Move to the next record.
            rs.MoveNext
            i = i + 1
        Loop
    Else
        'Close the recordet 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
    
    'Write the array values in the Excel sheet.
    Sheet1.Activate
    For i = 1 To UBound(myValues)
        For j = 1 To 5
        Cells(i + 1, j) = myValues(i, j)
        Next j
    Next i
    
    'Close the recordet and the connection.
    rs.Close
    con.Close
    
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    
    'Adjust the columns' width.
    Columns("A:E").EntireColumn.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, based on the Customers table.

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 external library is required.
    
    'Written by:    Christos Samaras
    'Date:          05/10/2013
    'e-mail:        xristos.samaras@gmail.com
    'site:          http://www.myengineeringworld.net
    '------------------------------------------------------------------------------------

    'Declaring the necessary variables.
    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strQuery    As String
            
    '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 adn 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 "Connection was not created!", vbCritical, "Connection 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
    
    If Not (rs.EOF And rs.BOF) Then
        'Write the query values in the sheet.
        Sheet2.Activate
        Range("A2").CopyFromRecordset rs
    Else
        'Close the recordet 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
    
    'Close the recordet and the connection.
    rs.Close
    con.Close
    
    'Release the objects.
    Set rs = Nothing
    Set con = Nothing
    
    'Adjust the columns' width.
    Columns("A:B").EntireColumn.AutoFit
    
    'Enable the screen.
    Application.ScreenUpdating = True

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

End Sub

Both codes use late binding, so no reference to external library is required.



Download it from here



Download

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



Read also



Read DBF Files Using VBA

Did you like this post? If yes, then share it with your friends. Thank you!



Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: tel. +30-6973513308, e-mail , Facebook , Twitter , Google+ and Linkedin. Full CV