UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Dynamic SQL    
Dynamic SQL

Dynamic SQL refers to the process of building a SQL statement at runtime which provides a wider ranger of functionality beyond what is available in queries object. Generally one would use VBA to build SQL statement with a String then execute it as a SQL. Because of two different context, this can create some trap for unwary novices.

Contents

When to use a Dynamic SQL

Because dynamic SQL allows us to do so many things, it's tempting to fall into the error of seeing nothing but nails holding a hammer. We should be sure to first ask the question of whether we really need dynamic SQL. We generally have two alternatives to dynamic SQL; Parameter Query and WhereCondition.

Using parameter queries can be comparatively foolproof and does not have problems of managing strings correctly but unfortunately, this is usually at expense of not always being optimized. When queries are created and at time of saving a new query, ACE will create an Execution Plan, but with parameters, it does not know the value and thus has to make some guesses. Sometime the guess may be bad and thus for certain given values at runtime, the parameter query may run more slowly than necessary. However, that becomes a nonissue when we use a parameter query to, for example, add a new row or perhaps update an existing row where the key is not parameterized and thus can always be executed optimally.

WhereCondition is fairly specific, and refers only to opening a form. Thus this is inapplicable for any queries that does not do a SELECT. However, because it's comparatively simple to supply just the Where clause upon opening the form, we have a powerful tool of managing what records will be displayed on form and tailor it for different instances. If the only part of SQL statement we are working that is dynamic is the WHERE clause, then we ought to be using WhereCondition. Whenever WhereCondition is supplied in the OpenForm arguments, Access will automatically append the string of WhereCondition to the existing SQL string, even if there's already a WHERE clause present and that is done prior to executing the statement as part of opening form so there is no "wasted records".

So, generally, we find dynamic SQL to be absolutely necessary when we have those requirements such as:

Different SELECT list (that is more common for queries based on Crosstab Query)
Tables to be selected may be different (common in import/export to/from a temporary table that does not have same name)
Different criteria for HAVING in aggregated queries
Unknown numbers of column to filter with unknown criteria (typically for a form where user can select different columns and specify arbitrary criteria each to filter upon)
Select a group of records based on a certain column contaiining a value matching any values in an arbitrary list specified by the user

There may be other cases not listed but in principle, the reasoning is similar; the information we need to effectively process the statement is not known until user supplies the information during running the application. While WhereCondition and Parameter Queries can parameterize away many of filter requirements, it cannot do so for where the query's outlay are materially different.


Executing a SQL Statement in VBA

There are several ways to execute a SQL via VBA; we will list all methods and examine the differences first. It is helpful to divide SQL statements into two broad classes; SELECT queries and Action queries which could be an UPDATE, DELETE, INSERT INTO or SELECT INTO, the difference being that Select queries do not create any data changes whereas Action queries will change data in some fashion. We need to use different methods for those two classes of queries.

For handling Select queries, we have the following methods available:

CODE
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT foo FROM bar;"

'OpenRecordset method
Set rs = db.OpenRecordset(strSQL)

'Binding to a form
Set Forms("MyOpenForm").Recordsource = strSQL

'Alternative method with same effect
Set Forms("MyOpenForm").Recordset = rs

rs.Close
Set rs = Nothing
Set db = Nothing

Generally speaking, we use OpenRecordset method to be then used in VBA. For fuller discussion, refer to Recordsets for Beginners which examine using VBA to manipulate recordsets. The last two methods both are functionally similar in that we can now change a form's recordsource at runtime which can be useful. Be aware that as earlier noted, one could use parameter queries and/or WhereCondition to accomplish similar things and usually with less effort.

We also have those methods available for Action queries:

CODE
Dim db As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO bar (foo) VALUES (100);"

'Using Execute method
db.Execute strSQL, dbFailOnError

'Using RunSQL method
DoCmd.RunSQL strSQL

Set db = Nothing

Note that there are other methods not covered which will be covered in last section.


Inserting in VBA variables

Now we've see few examples of how VBA would be used to execute different SQL statement, the examples also does not actually use dynamic SQL per se because there is no variables in the string which is the important aspect that also trips up people. When we have variables that contain some values we want to put in a SQL statement, we need to be sure concatenate it as a new string. We must remember that we are going to deal with two different context; VBA and SQL and generally speaking, SQL does not know anything about what VBA is doing, what variables VBA has in memory, among other things.

CODE
Dim intVariable As Long
Dim strSQL As String

'Assign a value
intVariable = 100

'Build the dynamic SQL
strSQL = "SELECT bar FROM foo WHERE baz = " & intVariable & ";"

'Display the new SQL statement
Debug.Print strSQL

Note that we do not actually embed the variable inside the string but rather outside the string so the output will be this:

CODE
SELECT bar FROM foo WHERE baz = 100;

We do not want to do that:

CODE
strSQL = "SELECT bar FROM foo WHERE baz = intVariable;"

which will give us an error "Too few parameters; expected 1". That error may appear to be odd and misleading. What happened is that ACE see the "intVariable" but does not realize it as a variable containing a value of 100 (that's VBA's variable so ACE could not possibly know about it). Because intVariable is not realized as a field or other ACE object, ACE then assumes that intVariable must be a parameter and thus treats the statement as if it was a parameter query but of course the parameter was not filled in because it was never a parameter to begin with, we get that mystifying error. Hence the need to ensure that our VBA variables are not actually embedded in the SQL statement but rather concatenated so that as a result, the value, not the variable name is embedded into the final SQL string.


Handling the Delimiters

Now, the next aspect that trips up people is handling string. Because we happen to use same delimiters to start and end a string in both VBA and ACE, this can be a frustrating exercise, and thus need to take additional steps to properly embed the string. Here's an starting point:

CODE
Dim strValue As String
Dim strSQL As String

strValue = "Hello, world!"
strSQL = "SELECT bar FROM foo WHERE baz = " & strValue & ";"

'See what the result is
Debug.Print strSQL

The output would be something like that:

CODE
SELECT bar FROM foo WHERE baz = Hello, world!;

If we were to go back to Query Designer and re-create same query, the Query Designer would probably output something like this:

CODE
SELECT bar FROM foo WHERE baz = "Hello, world!";

So, we're actually missing the delimiters for the string "Hello, world" inside the SQL statement. We already know that we need to properly delimit strings in VBA so this would break in VBA:

CODE
strValue = Hello, world! 'Error

but because we're embedding this string in a SQL statement that is expecting a string, we need to add the delimiter. Fortunately, we have three different ways to do just that.

CODE
strValue = "Hello, world!"

'Using different delimiter
strSQL = "SELECT bar FROM foo WHERE baz = '" & strValue & "';"

'Doubling up the delimiter
strSQL = "SELECT bar FROM foo WHERE baz = """ & strValue & """;"

'Using Chr(34)
strSQL = "SELECT bar FROM foo WHERE baz = " & Chr(34) & strValue & Chr(34) & ";"

The last two methods are functionally equivalent and will produce this output:

CODE
SELECT bar FROM foo WHERE baz = "Hello, world!";

First method is just so slightly different:

CODE
SELECT bar FROM foo WHERE baz = 'Hello, world';

ACE is tolerant and can process strings with either delimiters, providing we are consistent with using same delimiter to start and end.

But what about the poor O'Reilly?

However, there is one issue that threats to trip up the single quote delimiter. Consider what will happen if we have this:

CODE
strValue = "O'Reilly"
strSQL = "SELECT * FROM Customers WHERE LastName = '" & strValue & "';"

We're using single quote to delimit a string but how would ACE know which is the ending delimiter, the ' in the "O'Reilly" or the ' after the last name? It couldn't, and as soon as it runs into the ' between the O and Reilly, ACE will decide that the string we are trying to insert is just "O" and get confused at apparently non-valid token "Reilly'" and throw an error.

You may be now thinking, "So, we will use either doubling up or Chr(34) then." But what about a company that's named:

CODE
Mike's & Al's "Good" Cars

Simply doubling up the delimiter, whether we use single quote or double quote, will not work with that string because that string contains both of each. What are we to do? Incidentally, that is the advantage of parameter queries - once assigned the value, we do not have to worry about the various delimiters threatening to break our string. Fortunately, we still can get ourselves out of this mess by doing what is called "sanitizing the string".

Basically to sanitize the string, we need to double all instances of the delimiter being used inside the string. This time, we will get the string from a textbox control on a form rather than from a VBA variable to represent the case of processing the value as entered by the user.

CODE
strValue = Me.MyFilteringTextBox.Value 'Contains "Mike's & Al's "Good" Cars", with the delimiters included

'Sanitize the string, assuming we are using " as the delimiter
strValue = """" & Replace(strValue, """", """""") & """"
strSQL = "SELECT * FROM Companies WHERE CompanyName = " & strValue & ";"

The resulting SQL would probably look like this:

CODE
SELECT * FROM Companies WHERE CompanyName = "Mike's & Al's ""Good"" Cars";

Note we can do the same thing using the ' as the delimiter:

CODE
strValue = "'" & Replace(strValue, "'", "''") & "'"

with the following result:

CODE
SELECT * FROM Companies WHERE CompanyName = 'Mike''s & Al''s "Good" Cars';

Also, do note that the doubling up of delimiters will disappear when we actually execute the results. To better illustrate this, we will look at inserting the company:

CODE
strValue = Me.MyTextBox 'Contains "Mike's & Al's "Good" Cars"
strValue = "'" & Replace(strValue, "'", "''") & "'"
strSQL = "INSERT INTO Companies (CompanyName) VALUES (" & strValue & ");"
CurrentDb.Execute strSQL, dbFailOnError

The resulting record for newly inserted row will now contain this for CompanyName:

CODE
Mike's & Al's "Good" Cars

even though it initially came from this:

CODE
'Mike''s & Al''s "Good" Cars'


Conclusion

All in all, this has been an exercise in separating out the context of how we are processing strings, first as part of VBA string, then as a SQL statement into ultimately the final string that is the actual value. Because of those two contexts, we need to be careful that for each context, the string is properly delimited and sanitized to avoid dangling strings.

External Resources

For a great function library on processing dynamic SQL statement, download J Street Tech's SQL tools at this page

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 15,031 times.  This page was last modified 01:37, 10 February 2012 by Jack Leach. Contributions by George Hepworth and BananaRepublic  Disclaimers