Acess SQL

Mastering Access S Q L ( S t r u c t u re d
Query Language) can be a daunting
task even if you consider yourself
an Access expert. Part of the
p roblem is the absence of an Access-to-S Q L
i n t e rface. This limitation doesn’t have to slow
you down, however, if you know how to avoid
some of the more common coding mistakes. In
this article, we’ll show you how to use the query
design grid to create SQL statements and then
how to run the statements in the Immediate
window to debug them.
You might be wondering why you should
bother to learn SQL at all. One reason is that
almost any bound object will accept a S Q L
statement as its data source. Consequently, you
can often replace a fixed query with a simple
SQL statement. In addition, some queries simply
can’t be replicated in the query design grid.
Only a SQL statement can implement a Union
query, for instance. Once you’re familiar with
SQL, you’ll find many convenient uses for it.
BUILD A SQL STATEMENT IN THE QUERY
DESIGN GRID
SQL statements can be extremely long and, as
such, prone to logic errors and typos, so let
Access do as much of the work for you as possible.
Fortunately, you can create most basic
statements in the query design grid. Access
p roduces an equivalent SQL statement for every
query, and you can use this behaviour to your
advantage. To build a SQL statement in the
query design grid, begin as you would with
any normal query by choosing a data sourc e
(table or query), clicking on the New Object
button in the Database toolbar and selecting
Query. (In Access 97, you would select the
Queries tab and click the New button.)
Once you have a data source and have
opened the query design grid, drag fields fro m
the field list to the grid, build relationships, add
criteria and specify sort orders. Along the way,
feel free to view the results of the query by
clicking the View button and choosing
Datasheet Vi e w .
When you’ve gone as far as you can using the
grid – you may not be able to create the complete
statement – click the View button and
choose SQL View and Access will display the
query’s equivalent sql statement. Finally, highlight
the statement and copy it to a module.
In many cases, the resulting statement will
need some fine tuning. Unless you’re very adept
at writing SQL statements, though, you’ll pro bably
find this quick-start method preferable to
writing the entire statement from scratch.
DEBUG A SQL STATEMENT IN THE QUERY
DESIGN GRID
If you make any changes to a SQL s t a t e m e n t
after copying it from the design grid to a module,
chances are the statement won’t run correctly
the first time. Unfortunately, VBA e r ro r
messages aren’t very helpful in this context.
On the other hand, the query design grid is
almost always helpful and informative. If you
can’t quickly figure out the problem, copy the
SQL statement from the module to the query
design grid and run the statement there. The
same error will occur, but the query design
grid’s error message will be more specific and
will usually help you pinpoint the mistake.
If you’d like to try this yourself, here’s a short
list of instructions that will help you through the
p ro c e s s :
1 . Highlight the SQL statement in the module.
Don’t include the quotation marks at the beginning or the end of the statement or the V B A
method (Run SQL or Execute) you’re using to
run the SQL s t a t e m e n t .
2 . P ress Ctrl-C or, alternatively, choose Copy
f rom the Edit menu.
3 . Access the Database window (press F11 to
re s t o re the window if it’s minimised).
4 . Choose Query from the Object bar and then
click New in the Database window toolbar.
(Access 97 users should click the Queries tab
and then click the New button in the Database
w i n d o w . )
5 . In the resulting new query dialog, double
click Design Vi e w .
6 . When Access opens the query design grid,
close the Show Table dialog without selecting
a data sourc e .
7 . Click the View button in order to open the
SQL w i n d o w .
8 . P ress Ctrl-V or choose Paste from the Edit
menu in order to copy the SQL statement to
the SQL w i n d o w .
9 . Replace any variables with the appro p r i a t e
object names.
1 0 . Select Query | Run from the Access menu
bar (or click Run on the Query Design toolb
a r. )
In step 9, we note that you must replace variables
with the actual object names they re p resent.
For example, the statement:
SELECT * FROM " & strTable & "
would re t u rn an error if you tried to run it as is
in the query design grid. You must replace the
variable strTable with an actual table or query
name. Let’s suppose you’re working with a
table named tblMyTable. You’d replace the “ &
s t r Table & “ section of your statement with the
table’s name as follows:
SELECT * FROM tblMyTable
After replacing all the variables with actual
object names, run the statement by clicking the
Run button on the Query Design toolbar. If the
grid evaluates the statement without re t u rn i n g
an erro r, you can assume your problem was
with the variables. If not, most likely there’s a
simple problem with the delimiting characters
( which we’ll discuss in the next section).
When variables aren’t the problem, Access
will almost always display a more comprehensive
error message than you received fro m
vba. For instance, if your statement contains a
syntax erro r, Access usually indicates the
o ffending section – significantly narro w i n g
your search. At this point, you should be able
to spot your mistake and make the necessary
c h a n g e s .
Keep working with the statement until you
receive no errors, then copy the corrected statement
back to the module and re s t o re any variables
you replaced in step 9. Or make the necessary
corrections to the statement in the module
(as long as the concatenated variables
w e ren’t part of the original pro b l e m ) .
AVOID CONCAT E N ATION ERRO R S
Using the query design grid to track down pro blems
in SQL statements is a great trick, but
replacing the variables first can be a pain. Yo u
can easily make a mistake in the process and
not know it, further complicating your debugging
task.
You can avoid the variable problem by
adding a few extra lines of code to your V B A
p ro c e d u re. The additional code will print an
evaluated SQL statement in the Immediate window.
By evaluated, we mean that VBA w i l l
replace all of the variables with the appro p r iate
object names. Copying the evaluated version
from the Immediate window to the query
design grid relieves you of the aggravating task
of replacing the variables and completely eliminates
the possibility of introducing typos (and
hence additional errors) into your statement.
The code that follows shows an example of
this easy trick:
strCriteria = " = 'Smith'"
strSQL = "SELECT * INTO tblNewTable FROM
tblOldTable WHERE " & "tblOldTable
.LastName" & strCriteria & ";"
Debug.Print strSQL
The first two lines assign the search criteria
and the basic SQL statement to the string variables
strCriteria and strSQL. The Debug.Print
statement then prints the evaluated statement
to the Immediate window. If the statement
re t u rns an erro r, open the Immediate window
(by pressing Ctrl-G or clicking the Code button,
depending on what window is active). Now
copy the evaluated version
SELECT* INTOtblNewTableFROMtblOldTable
WHERE tblOldTable.LastName = 'Smith';
to the query design grid (instead of copying
the original statement from the VBA m o d u l e )
and run it there. Notice that the evaluated version includes the actual name of the data sourc e
and the criteria string, not the variables shown
in the code listing above.
Not only is this solution easier, but you avoid
i n t roducing typos. Don’t wait until you have a
p roblem with a SQL statement, though; get in
the habit of adding this functionality to your
code so it’s available when you need it.
QUICK RESULTS WITH THE QUERY BUILDER
Access SQL isn’t limited to queries and V B A
modules; controls often use a SQL s t a t e m e n t
instead of a saved query as the Row Sourc e
p roperty. Fortunately, you don’t have to cre a t e
the appropriate statement from scratch; you
can use the SQL statement query builder. (We ’ l l
show you how to access this builder in just a
minute.) In the open builder, you’ll choose
fields and express criteria in the query design
grid, just as you would in a normal query. The
builder then converts your work into a S Q L
statement. You don’t actually have to know
SQL at all.
Let’s take a look at an example. Open in
Design View any form that contains a combo
or list box, or open a blank form and add a
combo or list box. Double click the combo or
list box to open its property sheet, and click the
Build button that appears when you select the
Row Source property field. This launches the
SQL statement query builder, which is a simple
query design grid. Modify the grid as you
would a query. (If you’re working with an
existing control, the grid will display an equivalent
sql statement in the query design grid.)
F i g u re 1 shows a simple expression that will
display a list of concatenated fields – FirstName
and LastName. In other words, the combo or
list control will display a list of names.
You can view this query’s SQL statement, shown in Figu
re 2, by choosing SQL View from the Vi e w
button at the far left of the toolbar. You can
also see the results of the query by choosing
Datasheet Vi e w .
As you can see, this builder is flexible – you
can view three diff e rent forms of the same
query. You can bounce back and forth between
the builder, the SQL statement, and the re s u l t s
until you get the statement just right, at which
time you simply close the builder and save
your changes when prompted. The control will
update accord i n g l y .
SQL can be difficult to work with; even the
experts occasionally fret over a statement. Fortunately,
Access offers a number of tools to
make the task easier. The query design grid
gives you an easier way to create SQL s t a t ements
than trying to create them from scratch.
In addition, you can debug statements by copying
evaluated statements from the Immediate
window to the query design grid.