Access Hacks

Hack #74. Substitute Domain Aggregate Functions for SQL Aggregate Functions

Reduce the amount of code you enter and still get the same results.

Within VBA code, it is a common practice to tap into the ADO objects and use some SQL to query data in the database. Because SQL is the de facto standard for querying data, following this route is understandable. However, sometimes you don't need to query data in this way.

For example, if you need to process individual records, using ADO and SQL makes sense. A recordset is created that is typically scrolled through using the MoveNext method within a Do Until loop or similar construct.

On the other hand, ADO and SQL are sometimes used just to get an aggregate value from a set of records. In this situation, the individual records are of no concern. Instead, you're looking for a summary, such as a sum, a count, or an average.

The Code

Example 8-1 shows a routine that uses ADO and SQL to return the sum of some invoice amounts.

Example 8-1. Using ADO and SQL to return a sum

Sub get_SQL_Sum( )
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim rs As New ADODB.Recordset
  rs.Open "Select Sum(Amount) As SumOfAmount From Invoices" & _
    " Where InvoiceDate=#12/10/04#", _
           conn, adOpenKeyset, adLockOptimistic
  MsgBox rs.Fields("SumOfAmount")
  rs.Close
  Set rs = Nothing
  Set conn = Nothing
End Sub

The SQL statement includes the SQL aggregate Sum function. Also, the sum of the amounts is from a set of records filtered to a single invoice date ...