O'Reilly logo

Access 2003 VBA Programmer's Reference by Armen Stein, Graham Seach, Teresa Hennig, Patricia Cardoza

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

14.1. Working with SQL Strings in VBA

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

14.1.1. Building SQL Strings with Quotes

The first thing to learn about building SQL statements in VBA is handling 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, we need to replace the 17 in the above 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required