UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> SQL to VBA    
SQL to VBA

Often times we have situations where we want to use a SQL string defined in VBA. It can be tricky to build the SQL string through VBA: what works in the SQL Query Designer needs to be converted to a syntax where the VBA string output is identical to the SQL string that would be seen in the Query Designer, so when building the string in VBA, we must handle things such as making sure there's no spaces in line breaks, escaping double quotes, and, depending on the execution method, resolving any Expression Service strings.

This article will attempt to cover all of the standard requirements for converting an SQL string to one built in VBA.


Contents

VBA SQL String Example

Below is an example of a basic SQL string being built in VBA. Using VBA to build SQL strings is a powerful means of having dynamic queries, reducing the requirement for many saved queries.

Here is an SQL String as viewed in the SQL view of the Query Designer:

CODE
SELECT MyField
FROM MyTable
WHERE MyField = 0;

The SQL String put into a VBA variable:

CODE
Dim strSQL As String

strSQL = "SELECT MyField FROM MyTable WHERE MyField = 0;"


Debugging SQL Strings in VBA

Debugging SQL Strings built with VBA is essential. To do so, store the string in a variable (rather than applying the string directly to it's source), then use the Debug.Print statement before attempting to apply the SQL string. This will output to the Immediate Window the actual SQL String that Access will be attempting to process, which allows us to look at it and identify problems:

CODE
Dim strSQL As String

strSQL = "SELECT MyField" & _
 " FROM MyTable" & _
 " WHERE MyField = 0;"

Debug.Print strSQL

Me.MyListBox.RowSource = strSQL


Handling Spaces

Often times the base of the VBA string is taken from a Copy & Paste of the Query Designer SQL View. While this keeps us from having to copy much of the text into our VBA, it presents a few things that need to be fixed: one common pitfall being that the SQL Designer doesn't insert spaces at the end of each line shown. Thus, when we copy the SQL into our VBA and use Line Breaks to keep a similar formatting, we may end up with the following:

CODE
strSQL = "SELECT MyField" & _
 "FROM MyTable" & _
 "WHERE MyField = 0;"

Note that at the end of each line, where the quote closes the string, there's no space after the last field/condition (or, if preferred, spaces can be inserted before each SQL Keyword such as FROM and WHERE). When outputting the SQL String to the Immediate Window for debugging, the example above would produce the following output:

CODE
SELECT MyFieldFROM MyTableWHERE MyField = 0;

The lack of spaces before the FROM and WHERE keywords is problematic, as database engine does not recognize the keywords and errors while trying to process it.

There's two methods to ensure correct spacing.

Method 1: Adding a space at the end of each string

CODE
[code]strSQL = "SELECT MyField " & _
 "FROM MyTable " & _
 "WHERE MyField = 0;"

Method 2: Adding a space before each keyword:

CODE
strSQL = "SELECT MyField" & _
 " FROM MyTable" & _
 " WHERE MyField = 0;"

In method one, you can see that we've added a space at the end of each portion of the string (except the last one, where it's not required as there's no statements following it).

In method two, instead of adding the spaces at the end of the string, we add them to the beginning (except the first one, which isn't required as there's no preceding statements).

In either case, the output of the SQL String is now correctly spaced and valid:

CODE
SELECT MyField FROM MyTable WHERE MyField = 0;

(Method two may be more desirable as it provides a more uniform look when viewing the code: one can easily tell by quickly viewing the left side of the lines that spaces have been added, where looking at method one's syntax may be more difficult to notice due to varying line lengths).


Escaping Quotes

Another, often more difficult requirement is to escape the quotes that may exist in the source SQL String. Consider the following SQL String as viewed in the SQL View of the Query Designer:

CODE
SELECT MyField
FROM MyTable
WHERE MyField = "ThisValue";

When put into a VBA string without handling of the quotes, this causes an invalid syntax, as VBA uses quotes to denote the start and end of a literal string. The following, which is a common occurrence when copying SQL Strings, is invalid and will not compile, and thus cannot be processed by either VBA or the database engine:

CODE
strSQL = "SELECT MyField" & _
 " FROM MyTable" & _
 " WHERE MyField = "ThisValue";"

The quotes that carry over from the SQL String close the VBA string early: in this case, VBA reads the close of the string at the WHERE MyField = line, because the quote is not escaped.

The complete topic of escaping quotes in VBA is a broad one and out of the scope of this article. The examples shown are enough for the context of this article, though a more thorough study of the topic is recommended. Additionally, a function such as FixQuotes can be used at design time to aid in the conversion.

To escape a quote in VBA, replace the "inner" quote (the one that will be part of the SQL String output) with two quotes instead, so: replace " with "". This is a common and safe method to escape quotes in VBA. As such, our SQL String in VBA will now look like this:

CODE
strSQL = "SELECT MyField" & _
 " FROM MyTable" & _
 " WHERE MyField = ""ThisValue"";"

Using the aforementioned debugging method, check that the string output is as follows:

CODE
SELECT MyField FROM MyTable WHERE MyField = "ThisValue";


Resolving Expressions

Depending on the execution method of the SQL String (DoCmd.RunSQL vs. CurrentDb.Execute, for example), Expression Service expressions may need to be resolved. For details on the different methods and requirements, refer to the RunSQL vs Execute article. In the context of this article, it will suffice to know that RunSQL does not require pre-resolution of ES Expressions, and Execute does.

An Expression Service expression resembles the following: [Forms]![MyForm]![MyControl]

Consider the following SQL Statement:

CODE
SELECT MyField
FROM MyTable
WHERE MyField = "[Forms]![MyForm]![MyControl]";

When the Expression Service will not be available, this expression must be resolved in VBA and concatenated into the string, like so:

CODE
[code]strSQL = "SELECT MyField" & _
 " FROM MyTable" & _
 " WHERE MyField = """ & Forms!MyForm!MyControl & """;"

Again, verify the output using the debugging method described above. Given the control value of ThisValue, the valid output should be as follows:

CODE
SELECT MyField FROM Mytable WHERE MyField = "ThisValue";


Tools

A few tools have been created and made available to handle converting from SQL to VBA for you. One such tool is Allen Browne's implementation, which can be found here: http://allenbrowne.com/ser-71.html

Edit Discussion
Custom Search


Thank you for your support!
This page has been accessed 5,354 times.  This page was last modified 14:54, 13 April 2013 by Jack Leach.   Disclaimers