Recipe 4.9 Clean Test Data out of a Database When You're Ready to Ship It
designing and building a database; it's ready to
ship to your client. Before they can use it, you need to remove the
artificial data you've entered, without destroying
permanent lookup tables. Is there a simple way to do this without
running into referential-integrity problems?
One solution is to open every data table in datasheet view, select
all the records, press the Delete key, and confirm the deletion.
However, there are three problems with this simple method:
You have to open tables in a
particular order (i.e., tables on the many side of a many-to-one
relationship before their related one-side tables).
You have to remember which tables contain test data and which ones
contain production data.
The task is tedious and repetitive.
Instead of clearing out your
test data by hand, you can write a general-purpose routine that uses
a table of tables and a simple SQL statement to remove only the test
data, in the correct order.
Open 04-09.MDB and view the tables in the
database container. Open the tblFood table and try to delete some
records. You'll get a referential-integrity error,
because there are related records in txrefFoodRestaurant. Figure 4-17 shows the relationships set up for the sample
database. Now open frmDemo and click on the Clear button to remove
all the test data from the database without any manual intervention.
Figure 4-17. Relationships in the sample database
To implement this technique in your own database, follow these steps:
Import the table zstblDeleteOrder (structure only, without data) into
your own database, or create a new table with the fields shown in
Table 4-4. Structure of zstblDeleteOrder
| || |
Import the module zsbasMaintain into your database, or create a new
module with the single function shown here:
Public Function acbClearData( ) As Boolean
' Remove all data from tables specified in zstblDeleteOrder.
' Data is removed in the order specified to avoid
' referential-integrity violations.
On Error GoTo HandleErr
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb( )
Set rst = db.OpenRecordset("zstblDeleteOrder", dbOpenSnapshot)
Do Until rst.EOF
db.Execute "DELETE * FROM " & rst("TableName")
Set rst = Nothing
acbClearData = True
acbClearData = False
MsgBox "Error " & Err & ": " & Err.Description, , "acbClearData( )"
Open zstblDeleteOrder in datasheet view and add one record for each
table you want to clear out before shipping. These tables must be
listed in the order in which you want them cleared. Assign each table
a unique order number, with the lowest number belonging to the first
table to be cleared. Tables on the many side of a one-to-many
relationship should be listed before tables on the one side of the
relationship. Tables that you don't want to clear
(including zstblDeleteOrder) should not be entered at all. Figure 4-18 shows the sample version of zstblDeleteOrder.
Figure 4-18. Sample zstblDeleteOrder
If you'd like a form to
control the deletion process, create a new, blank form. Place one
command button on the form and modify the command
button's Click event handler to call acbClearData:
Private Sub cmdClear_Click( )
The acbClearData function automates the task of
selecting the order of your tables and then deleting the data table
by table. You select the order when you build the zstblDeleteOrder
table. The function works by opening a snapshot of this table and
looping through the snapshot one line at a time. The line in the
function that does the actual work is:
db.Execute "DELETE * FROM " & rst("TableName")
This line concatenates the table name found in rstTables, using SQL
keywords to form a complete SQL statement. For example, if you
specify tblFood as one of the tables to delete, Access builds the
following SQL statement:
DELETE * FROM tblFood;
This is the SQL equivalent of a delete
query that selects all rows from the table and deletes them. The
db.Execute statement turns this query over to the
Jet engine for execution.
The sample database has a second button, Restock, on the demo form.
This button runs a procedure that in turn runs four append queries to
take backup copies of the data and return them to the main data
tables. This lets you test the function in the sample database more
When you use this technique in your
own database, be sure to compact the database before you distribute
it to your users. To do this, select Tools Database
Utilities Compact and Repair Database. There are two
reasons to compact your database at this point:
Until you compact, the Access file won't shrink at
all. When you delete data from tables, Access marks the data pages as
empty, but it doesn't give them back to your hard
drive as free space. This occurs only when you compact the database.
When you compact a database,
Access resets the next counter values for all incrementing autonumber
fields. If you remove all the data from a table with an autonumber in
it and compact the database, the next record added will have an
autonumber value of 1.
4.9.4 See Also
For more information on using DAO in Access databases, see
How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.