Microsoft Office Tutorials and References
In Depth Information
SQL and Recordsets
If the SQL statement isn’t already selected, drag the mouse pointer through
the entire SQL statement to select it and then press Ctrl+C. After you copy
the SQL statement to the clipboard, you can paste it into a myRecordset.
Open statement in VBA code where indicated by the SQL statement here:
myRecordset.Open “ SQL statement here ”, , CursorType
Unfortunately, just pasting the SQL statement isn’t quite enough to get the
job done. You have to change some things in the VBA code, as follows:
✦ Remove the semicolon (;) from the end of the SQL statement.
If the pasted SQL statement breaks across multiple lines, gather the lines
together into one long line (or break up the line by using the continuation
character, as we discuss in the next section).
✦ If the SQL statement contains any double quotation marks (“), replace
them with single quotation marks (‘).
Take a look at Figure 5-4, earlier in this chapter, for an example of a big SQL
statement. The first step is selecting the SQL statement by dragging the
mouse pointer through it until you highlight all the text. Then press Ctrl+C,
or right-click the highlighted text and choose Copy from the contextual
menu to put a copy of the SQL statement on the clipboard.
Within your procedure in the Code window, type the recordset.Open
statement, followed by two sets of double quotation marks. Place the cursor
between the two quotation marks, as in the following example (where the |
character represents the cursor):
Press Ctrl+V to paste the SQL statement between the quotation marks.
The cursor lands at the end of the SQL statement, just to the right of the
semicolon at the end of the statement. Press Backspace to delete the
If the SQL statement breaks into multiple lines, you need to unbreak it. Move
the cursor to the end of the first line. If a quotation mark is at the end of the