Simplify Your Error-Handling Code when Creating and Deleting Tables
This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
Simplify Your Error-Handling Code when Creating and Deleting Tables
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.by Susan Sales Harkins
There are several fairly simple ways to run a query through code. You can run a fixed query using the OpenQuery method. Or you can execute an SQL statement using either the Execute or the RunSQL method. The Execute method has more options and, therefore, may be more useful, but it has one limitation. Unlike the RunSQL and OpenQuery methods, Execute won't replace an existing table when running an INTO statement. This means that if you're using the Execute method, you must delete an existing table before you run an INTO statement that creates a table with the same name.
When it comes to deleting tables, it's easy to fall into the trap of creating overly complex code to detect existing objects and their current states. Fortunately, there are easy techniques you can use to get the job done that don't require a lot of convoluted code. In this article, we'll show you how to determine whether a table exists and, if necessary, how to delete it. We'll also show you how to determine whether or not a table is open before you delete it and how to set up your code to react appropriately--without a complex error-trapping routine.
We'll show you how to check for a table's existence when INTO is involved, since such situations are perfect examples of when you need to apply our techniques. However, you aren't limited to these situations. You can often use our methods whenever you need to delete or check for the existence of tables. In the case of an INTO query, running the query with the Execute method raises an error resembling what's shown in Figure A if the target table already exists.
Figure A: The Execute method can return this error when running the INTO SQL statement.
To get around this error, all you do is include the DeleteObject method in the format
DoCmd.DeleteObject acTable, tablename
where tablename identifies the table you need to delete before creating it with the Execute method. Now, let's suppose the table doesn't exist. In that case, the above statement will create an error, which is handled nicely with an On Error statement in the form
strategically positioned just before the DeleteObject method. When Access runs the DeleteObject statement and doesn't encounter the appropriate table, the preceding Resume Next statement will capture the error and VBA will continue to the next statement, with no harm done. If tablename does exist, VBA simply deletes it.
On Error Resume Next
A simple example
Let's look at this shortcut at work. First, create a table named tblNames that has the following Text fields: strFirstName, strLastName, and strTitle. Save the table, allowing Access to add a primary key, and then populate a few records. Next, create a new module and enter the code shown in Listing A.
Listing A: SQLTable1 code - no error handling
Sub SQLTable1() Dim db As Database, strSQL As String Set db = CurrentDb strSQL = "SELECT * INTO tblSQL FROM " & _ "tblNames ORDER BY strLastName" DoCmd.DeleteObject acTable, "tblSQL" db.Execute strSQL End Sub
Then, click the Go/Continue button (Run Sub/UserForm in Access 2000) to run the procedure. SQLTable1 will attempt to create a table named tblSQL. Since tblSQL doesn't exist, the DeleteObject statement raises an error resembling what's shown in Figure B. Note that the error number and message may be slightly different, depending on your version of Access.
Figure B: If the table that DeleteObject tries to delete doesn't exist, you'll receive this error message.
Now, enter and run the procedure shown in Listing B.
Listing B: SQLTable2 code - simple error handling
Sub SQLTable2() Dim db As Database, strSQL As String Set db = CurrentDb strSQL = "SELECT * INTO tblSQL FROM " & _ "tblNames ORDER BY strLastName" On Error Resume Next DoCmd.DeleteObject acTable, "tblSQL" On Error GoTo 0 db.Execute strSQL End Sub
This procedure will successfully create the table because the Resume Next statement passes control to the next statement instead of returning the error. The Resume Next error handling will stay in effect until you turn it off, so we used the statement
to disable the error handling. The next statement is the Execute statement, which creates the new table.
On Error GoTo 0
One small problem
There's one problem you may encounter when using our shortcut technique as written in Listing B. If there's any possibility that tblSQL may be open when you run the procedure, you can potentially encounter another error when the code attempts to delete the table. You see, VBA won't delete an open table, and since we're ignoring any errors caused by the DeleteObject method, we won't know that VBA didn't successfully carry out the method. To avoid this problem, we'll close the table before attempting to delete it. We won't have to perform a complex procedure to determine whether or not the table is open--we'll simply use the Close method and protect our procedure from errors using the Resume Next statement as we did before.
The Close method in Listing C will close tblSQL if it's open. If tblSQL isn't open, the Resume Next error will inhibit the resulting error.
Listing C: SQLTable3 code - close an open table
Sub SQLTable3() Dim db As Database, strSQL As String Set db = CurrentDb strSQL = "SELECT * INTO tblSQL FROM " & _ "tblNames ORDER BY strLastName" On Error Resume Next DoCmd.Close acTable, "tblSQL" DoCmd.DeleteObject acTable, "tblSQL" On Error GoTo 0 db.Execute strSQL End Sub
A couple of thoughts
Some people get a little queasy at the thought of deleting a table full of data. Fortunately, there's an easy cure. Simply rename the table instead of deleting it. For instance, you could replace the DeleteObject statement in Listing C with the following:
Doing so will rename tblSQL to tblSQLx.
DoCmd.Rename "tblSQLx", acTable, "tblSQL"
You can't always use error-handling shortcuts like the ones discussed in this article. Sometimes there's no getting around using complex error-trapping procedures. However, when a quick fix is all you need, don't work any harder than you have to. The simple techniques we've employed to reduce code are often appropriate--and you don't need to be working with an SQL INTO statement to take advantage of them.
Copyright © 2000 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.