Access™ 2007 VBA Programmer's Reference

15.1. Working with SQL Strings in VBA

To build SQL statements in VBA, you usually load them into string variables by concatenating various phrases together. Some of the phrases are exact SQL text that you supply, while others are the contents of variables in VBA or controls on forms or reports. When the SQL statement is complete, you can use it in queries, in the RecordSource of forms or reports, or in the rowsource of combo boxes or list boxes. This enables you to deliver power and flexibility in your Access applications.

15.1.1. Building SQL Strings with Quotes

The first thing to learn about building SQL statements in VBA is how to handle concatenation and quotes. They may seem simple, but many programmers have stared at VBA strings with multiple nested quotes and struggled to make them work.

Consider a SQL string that selects a record for a particular business from a table of businesses:

Select * From tblBusiness Where BusinessKey = 17

In actual usage, you replace the 17 in this statement with the BusinessKey that the user is currently working with. To build this SQL statement in VBA, using the BusinessKey from the current form, you would use something like this:

strSQL = "Select * From tblBusiness Where BusinessKey = " _
& Me!BusinessKey

One reason this is so simple is that BusinessKey is a numeric value. In SQL, numeric values are just stated, without quotes around them. This is great for primary key values, which are often AutoNumbers (that use the Long Integer data type). ...