Microsoft Office Tutorials and References
In Depth Information
SQL and Recordsets
The following example shows an original SQL statement. (Just imagine that
the code stretches out as one long line, which the margins of this topic
prevent us from showing.)
SELECT Orders.*, [Address Book].* FROM [Address Book] INNER JOIN Orders ON
[Address Book].ContactID = Orders.ContactID WHERE ((([Address Book].
State)=”NY”)) ORDER BY Orders.[Order Date];
The following statements show some VBA code to store that SQL statement
in a mySQL string variable. The myRecordset.Open statement creates the
recordset from the SQL statement.
‘Form a SQL statement from “chunks”.
Dim mySQL As String
mySQL = “SELECT Orders.*, [Address Book].* FROM [Address Book]” & _
“ INNER JOIN Orders ON [Address Book].ContactID = Orders.ContactID” & _
“ WHERE ((([Address Book].State)=’NY’))” & _
“ ORDER BY Orders.[Order Date]”
‘Fill the recordset with data defined by the SQL statement.
myRecordset.Open mySQL, , adOpenStatic, adLockOptimistic
Notice a few essential characteristics of this code:
✦ Each chunk of the SQL string is enclosed in double quotation marks.
✦ The blank space after a word is included at the beginning of each
✦ The ampersand and continuation characters, separated by single blank
spaces, end each line.
✦ The myRecordset.Open statement uses the mySQL variable name in
place of the lengthy SQL statement.
Figure 5-5 shows how the preceding SQL string looks in the Code window.
stored in a