Access and SQL
Part 3: Some Practical Examples
Here are a few examples of SQL being implemented from VBA. Future
tutorials in this series will expand on these techniques, and show
you more things you can do with SQL in your Access databases.
Using DoCmd.RunSQL to Run Action Queries
The DoCmd object in VBA can be used to perform a wealth of
different actions including one called RunSQL. You can't
run any sort of SQL statement using the RunSQL method, it
is specifically for running the type of queries that Access calls
"action queries". These include Delete Queries (used to
delete records from a table), Append Queries (used to add
records to a table), Update Queries (used to edit records
in a table) and Make Table Queries (used to create a new
table).
Working from the Access query design window you are limited to the
four query types described above, but using SQL in conjunction
with VBA (or by entering SQL directly into the SQL view of the
Access query design window) you can accomplish a lot more,
including the use of "data-definition queries" which are used to
build and modify the structure of the database itself.
The RunSQL method prompts you for two arguments, the SQL
Statement itself which must be supplied as a string (i.e. it
should be enclosed in quotes) and Use Transaction which is
optional:
The second argument concerns Transaction Processing and
assumes True if you omit it. When transaction processing is
applied to a query, Access first performs a "dry run" of the query
during which it writes all the changes to a temporary log file but
does not make any permanent changes to the database. If the query
finishes its task without any problems, the changes noted in the
log file are applied and the job (the transaction) is
completed. If, however, Access encounters problems whilst
executing the query, the transaction terminates and the log file
is discarded without any changes being made. Transaction
processing is a very useful safeguard for your data and should
always be applied, unless you have a particular reason not to do
so.
The following exercises demonstrate some of the things you can do.
You can work in an existing database or create a new one.
Build a New Table
- Open a VBA code window (Access 2000/2002: use Alt+F11,
Access 97: go to the Modules tab and click New).
For the purpose of this exercise, you will run the code directly
from the Immediate Window. This window allows you to
implement a code statement directly by typing in into the window
(it has to be typed as a single line) and pressing Enter.
- Open the Immediate Window by pressing Ctrl+G. In
Access 2000/2002 the Immediate Window will appear, usually docked
to the lower edge of the Visual Basic Editor window. In Access 97
a new window (the Debug Window) will appear, divided
horizontally into two sections - use the lower section of
this window.
- Enter the following line of code as a single line, then press
Enter:
DoCmd.RunSQL "CREATE TABLE tblTest ([StaffID] COUNTER
CONSTRAINT ndxStaffID
PRIMARY KEY, [FirstName] TEXT(25),
[LastName] TEXT(30), [BirthDate]
DATETIME);"
- Switch to the Access database window (press F11) and
move to the Tables tab. (NOTE: if the Tables tab was
already displayed, refresh the view by switching to a different
tab then back to the Tables tab again). You should see that a new
table (tblTest) has been created.
Take a look at the table. You will see that it contains the
four fields specified in the SQL statement:
Switch it into design
view and see that the data types are as specified, with the text
field of a specified size, and that the StaffID field is an
autonumber field and also the primary key field:
If you try to run the same line of code again an error occurs
because a table with the specified name already exists:
A method for dealing with this eventuality will feature in a
later tutorial in this series.
Add Records to a Table
Lets add some data to the table. Close the table if it is open,
and return to the Immediate Window.
- Enter the following line of code as a single line, then press
Enter (feel free to insert your own details!):
DoCmd.RunSQL "INSERT INTO tblTest ([FirstName], [LastName], [BirthDate])
VALUES ('Martin', 'Green', #09/27/1950#);"
Note the single quote marks around the text values Martin
and Green, and remember that single quotes are used here so
as not to conflict with the double quotes that enclose the
complete SQL statement. Note also that the date value is enclosed
by hash marks (#) and that the date is supplied in US
(m/d/y) format.
- Switch to the Access database window and open the table. You
will see your new record. Because the StaffID field is an
autonumber field its value is assigned automatically. It
does not need to be specified in the SQL.
Before the SQL is executed, Access displays a message asking
permission to add a record to the table. This is usual when Access
performs any action query, and can be suppressed with VBA code
if you don't want your users to see it:
If the user decides not to append the record they
can click the No button and the action is cancelled without
any further consequences, but when this happens when the SQL
statement is being run from VBA an error occurs:
So, if you want to give the user the option to
cancel the record, your code will have to handle the error when it arises.
Add a Field to a Table
SQL can be used to make changes to the structure of
an existing table. Fields can be added, removed or changed. Here's
how to add a new field. Close the table if it is open, and return to
the Immediate Window.
-
Enter the following line of code as a single line, then press
Enter:
DoCmd.RunSQL "ALTER TABLE tblTest ADD COLUMN [Age] BYTE;"
-
Switch to the Access database window and open the
table. You will see a new field has been added.
If you take a look at the table's design view you
will see that the SQL has also assigned the byte data type to
new field.
Modify Existing Records
In addition to working with the structure of a
table, SQL can be used to modify existing data. You may have used an
Access Update Query. This example uses the same method from VBA. We
have added a new field to the table, now we can enter some data.
First, an example of updating specific records by
adding a WHERE clause to the SQL statement:
-
Enter the following line of code as a single line
into the Immediate Window, then press
Enter (substituting the appropriate criteria to your
FirstName and LastName fields):
DoCmd.RunSQL "UPDATE tblTest SET [Age]=52 WHERE [FirstName]='Martin'
AND [LastName]='Green';"
As when you added records to the table, Access
displays a confirmation message when records are about to be
updated. Remember that cancelling the update will raise an error in
VBA.
If you do not include a where clause the SQL UPDATE
statement will modify all the records in the table. This may be
appropriate if you want to apply the same value to all the records,
or if you want to calculate a value making use of existing data:
-
Enter the following line of code as a single line
into the Immediate Window, then press
Enter:
DoCmd.RunSQL "UPDATE tblTest SET [Age]=Int((Date()-[BirthDate])/365.25);"
Instead of applying a specific value, this SQL statement performs
a calculation on each record making use of the value already present
in the BirthDate field and the Access Date() function
to calculate each person's age:
NOTE: It isn't good database practice to store calculated data in
a table that also contains the data from which it was calculated.
Why? Mainly because it wastes space. If you know a person's birth
date you can calculate their age at any time using a query. Also, if
you store their age as a number it will not update itself as time
passes, so eventually it will become incorrect. But I was stuck for an
idea so I allowed myself an exception to the rule! Delete a Table
It is just as easy to delete things with SQL as it is to create
them. Records can be deleted, as can fields and even entire tables:
- Enter the following line of code as a single line
into the Immediate Window, then press
Enter:
DoCmd.RunSQL "DROP TABLE tblTest;"
You won't see any warning message, but you will find that the
table has gone (a bit too easy for comfort!). You might need to
refresh the database window as described earlier before the table's
entry is removed.
Summary
These practical examples have demonstrated how you can manipulate
a database's structure and its data by implementing SQL statements
with VBA, working independently of the Access query tool. They show
the potential of working directly with SQL from your VBA procedures
to build, modify and populate tables with ease. I have
shown only a few examples of what can be done. SQL is capable of a
great deal more. Future tutorials in this series will explore
practical uses for these techniques, as well as the more familiar
uses of SQL to interrogate data and supply the user with
information. What's Next?
The next tutorial in this series illustrates how you can build
and modify queries "on the fly" by working directly with the query's
SQL, and includes a practical project to build a multi-purpose
query:
Access and SQL Part 4: Building Queries "On the Fly" |
