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 theRunSQL 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

1. 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.
2. 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 sectionof this window.
3. 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);"
    
4. 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.

1. 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.
    
2. 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.

1. Enter the following line of code as a single line, then press Enter:
   
   DoCmd.RunSQL "ALTER TABLE tblTest ADD COLUMN [Age] BYTE;"
2. 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:

1. Enter the following line of code as a single line into the Immediate Window, then press Enter (substituting the appropriate criteria to your FirstNameand 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:

2. 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:

1. 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"