Access™ 2007 VBA Programmer's Reference

15.4. The ReplaceOrderByClause and ReplaceWhereClause Functions

It's often necessary to "cut and replace" the Where and Order By clauses of a SQL string using VBA. Throughout this chapter, the ReplaceWhereClause and ReplaceOrderByClause functions are used to do that. This section shows you the code that's been doing all that hard work!

This first procedure ParseSQL does the "heavy lifting" of the SQL handling functions. It breaks up the original SQL string into components, so that individual pieces can be replaced. Although ParseSQL is Public, it's rarely called from anywhere other than the ReplaceWhereClause and ReplaceOrderByClause functions that follow it.

Please note that you don't have to type all this code into your application! It's in the Chamber Application file included with this chapter's download code.

Option Compare Database
Option Explicit
Public Sub ParseSQL(strSQL As Variant, strSELECT As Variant, strWhere A
Variant, strOrderBy As Variant, strGROUPBY As Variant, strHAVING As Variant)

On Error GoTo Error_Handler
'
'This subroutine accepts a valid SQL string and passes back separated
'SELECT, WHERE, ORDER BY, and GROUP BY clauses.

'
'INPUT:

' strSQL valid SQL string to parse

'OUTPUT:
' strSELECT   SELECT portion of SQL (includes JOIN info)
' strWHERE   WHERE portion of SQL
' strORDERBY ORDER BY portion of SQL
' strGROUPBY GROUP BY portion of SQL
' strHAVING  HAVING portion of SQL

Access queries have a semicolon (;) at the end of their SQL statements. While the subroutine ...