Using Access VBA to Query a SQL Server Database

Although we can create Linked Tables in Access that pull information from a SQL Server Database there are times when you want to check a value or lookup a value from an underlying SQL Server database directly from an Access Query.

We will look at an example that enables us to lookup a customer’s total revenue value from an Access Query.

The VBA To Query a SQL Server Database

The following VBA Function can be used from within an Access VBA Project. You will, however, need to add the ADODB library to the project first.

Public Function LookupAWCustomerRevenue(intID As Long) As Currency
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strConnString As String
 
    If intID = 0 Then
        LookupAWCustomerRevenue = 0
    Else
        strConnString = "Provider=SQLOLEDB;Data Source=W10NBMJD\SQL2014;" _
                        & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"
        
        Set conn = New ADODB.Connection
        conn.Open strConnString

        Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)
        
        If Not IsNumeric(rs.Fields("CustRev").Value) Then
            LookupAWCustomerRevenue = 0
        Else
            LookupAWCustomerRevenue = rs.Fields("CustRev").Value
            rs.Close
        End If
    End If
    
End Function

In this example we use an ADO connection to connect to a SQL Server instance (DBSRV\SQL2014):

        strConnString = "Provider=SQLOLEDB;Data Source=DBSRV\SQL2014;" _
                        & "Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;"

• The Provider parameter indicates that an OLDEB connection will be established and the Data Source parameter points to the SQL Server Instance.
• The Initial Catalog parameter identifies the database to be queried (AdventureWorks2014)
• The Integrated Security parameter indicates that Windows Authemtication will be used to authenticate with SQL Server.

A RecordSet object (rs) is used to create a record set from a SELECT statement:

        Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)

The SELECT statement is constructed from a literal string and the value for the variable intID that is passed into the function when it is called.

The If statement at the start checks for an intID value of 0. Integer variables default to a value of zero if not initialised (in other words if no value is provided when the function is called). If no value is passed in to the function a value of 0  is returned as the revenue value.

        If intID = 0 Then
               LookupAWCustomerRevenue = 0

The second If statement tests for a non numeric value being returned by the SELECT statement. If a customerID passed in to the function is valid, but they have plaved no orders the SUM(TotalDue) expression will return a NULL value. If this happens then the funvtion will return a value of 0 instead.

        If Not IsNumeric(rs.Fields("CustRev").Value) Then
            LookupAWCustomerRevenue = 0

The NULL value scenario can be seen in the following screen shot.

I placed a breakpoint on the line of VBA code containing the If statement and opened the Locals Window so that I could see all the variable values at that point of execution.

I tested the function by executing it from the Immediate Window in the VBA editor:

        ?LookupAWCustomerRevenue(1)

With a breakpoint set the code execution automatically stops at the marked line and enables us to view the environment at that point of execution.

The Locals Window in the above screen shot shows the Recordset object variable rs, and specifically the value for the first field from rs, “CustRev”. We can see that it is set to Null. This is because a Customer with CustomerID value 1 has not placed any orders and, therefore, has no resulting revenue value.

The following screnn shot shows that the query returns NULL when run directly on the SQL Server instance:

Assuming that a valid CustomerID is passed into the function and a non NULL value is returned by the SELECT statement, the function will return the total sales revenue for that customer as a currency value.

        LookupAWCustomerRevenue = rs.Fields("CustRev").Value

Calling The VBA Function From An Access Query

Calling a VBA function from an Access Query is simple. When you construct an Access Query you can create expressions from the built-in library of Access Functions. Any VBA functions thta you have created in an Access Project are also availablefor these expressions. The following screen shot shows this:

The fourth column shows the expression for retrieving the customer’s total revenue figure – it calls the VBA function called LookupAWCustomerRevenue.

The following example shows the results from this query: 

Summary

In this article we have seen a bit of VBA coding, some VBA Error Handling and Debugging techniques, and we have seen how we can call VBA functions from an Access query. Feel free to email us at info@ptr.co.uk if you have any questions relating to this article. There is also an Excel version of this article.

If you would like to learn more about any of the techniques seen in this article or VBA programming why not take a look at our Excel and Access VBA Training courses.

If you would like to learn more about working with a SQL Server Database then take a look at our SQL Server Training Courses.

You might find that these courses come up on a Late Availability offer from time to time, offering savings of 30%.