Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

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:

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.

Table 4-4. Structure of zstblDeleteOrder

Field name

Data type

Field size

Properties

Order

Number

Integer

PrimaryKey

TableName

Text

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

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( )
    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.

[ Team LiB ]