Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 4.9 Clean Test Data out of a Database When You're Ready to Ship It

4.9.1 Problem

You're finished
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?

4.9.2 Solution

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:

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

2. 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")
   rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
   acbClearData = True
   ExitHere:
   Exit Function
   HandleErr:
   acbClearData = False
   MsgBox "Error " & Err & ": " & Err.Description, , "acbClearData( )"
   Resume ExitHere
   End Function

3. 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

4. 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( )
   Call acbClearData
   End Sub

4.9.3 Discussion

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
than once.

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.