Storing SQL statements in variables

You can store SQL statements in variables, just as you can store text in variables. This can help with those extremely long SQL statements that seem to extend out forever past the right margin of the Code window. Many programmers will use this technique of building a long SQL statement out of smaller chunks, storing the statement in a variable. As an example, here is a series of VBA statements that build and execute a single length SQL statement from smaller chunks

'Create string variable (storage place) named mySQL. Dim mySQL As String

'Add lengthy SQL statement to mySQL in chunks. mySQL = "UPDATE Orders SET"

'Leading spaces below ensure spaces between words. mySQL = mySQL + " InvRecPrinted = True, Label Printed = True" mySQL = mySQL + " WHERE (((PONumber) Is Null)" mySQL = mySQL + " AND ((CCType)='MC'))"

'Line above uses single quotation marks inside double quotation marks.

'Now, mySQL contains the complete SQL statement, 'so hide warnings and execute the SQL statement. DoCmd.SetWarnings False DoCmd.RunSQL mySQL

'Update query has now been performed. Back to normal warnings. DoCmd.SetWarnings True

For the goods on variables, read about storing data in variables and constants in Chapter 4.

As daunting as the preceding code looks, it's not so bad if you read it as it would execute, one step at a time from top to bottom. The first statement, Dim mySQL As String, sets aside a little cubbyhole of storage space in which you can store some text. In code, refer to the contents of that cubbyhole as mySQL (although I could have used any name here).

The next statement, mySQL = "UPDATE Orders SET", stores the chunk of text in the quotation marks in the mySQL variable. So now the cubbyhole contains "UPDATE ORDERS SET".

The next statement changes the contents of that variable by creating a new string that consists of the current contents of the variable (mySQL) plus (+) the string " InvRecPrinted = True, LabelPrinted = True". By the time that line is finished being executed, the mySQL variable contains UPDATE Orders SET InvRecPrinted = True, LabelPrinted = True. Notice the addition of the blank space at the start of the string. That blank space is also added onto the string to make sure there's a blank space between SET and InvRecPrinted.

The following two lines of code do the same as the previous line in that each adds more text to the string stored in MySQL. The mySQL = mySQL + " WHERE (((PONumber) Is Null)" statement tacks part of a WHERE clause (criterion) onto the string (again preceded by a blank space). Then the statement mySQL = mySQL + " AND ((CCType) = 'MC'))" tacks on a blank space and its chunk of text.

The single quotation marks inside the string are required to avoid conflict with the double quotation marks surrounding the whole string.

By the time that the final MySQL = MySQL + ... statement has executed, the variable named mySQL contains the following SQL statement, which exactly matches all the syntax required of a valid SQL statement. (The statement is too lengthy to show on one line in this book, but in the mySQL variable, it definitely is one long valid SQL statement. (Like most SQL statements that you'll see in this book, the example below is just a copy-and-paste job from a query's SQL view.)

UPDATE Orders SET mySQL = mySQL + " InvRecPrinted = True, LabelPrinted = True WHERE (((PONumber) Is Null) AND ((CCType)='MC'))

The next statement in the code, DoCmd.SetWarnings False, just hides the warning message that action queries otherwise show. Then comes the actual execution of the SQL statement in the following statement:

DoCmd.RunSQL mySQL

By the time VBA gets to this statement, it knows that the name mySQL refers to a cubbyhole that I told it to create earlier. So it knows that it really needs to replace the name mySQL with the contents of the variable named mySQL before it does anything else. First, it does a quick substitution, replacing the variable name with its contents, as follows:

DoCmd.RunSQL UPDATE Orders SET InvRecPrinted = True, LabelPrinted = True WHERE (((PONumber) Is Null) AND ((CCType)='MC'))

The preceding statement is what VBA actually does when it executes the statement. It runs the update query specified in the SQL statement. (Technically, it's all executed as one long line — it's just too wide to show it that way here in the book.)

With the action query finished, the next statement — DoCmd.SetWarnings True — sets the warning messages back to their normal status.

Because code is building the SQL statement, the code can also make decisions about how to build the statement along the way. Thus, a VBA procedure could actually customize a SQL statement to a particular need or situation. In short, a procedure can make decisions about how to "write itself" before it executes itself. Funky but true.

Creating Tables from VBA

As you (hopefully) know, you can create tables in Access interactively, using Table Design. If you've ever created a Make-Table action query, you know you can build a new table from any existing table or query. VBA can also create new tables, either from existing tables and queries or from scratch.

0 0

Post a comment