3.3 Create a Report with Multiple Columns

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.

    

   Table 4-4. Structure of zstblDeleteOrder

   Field name	Data type	Field size	Properties
   Order	Number	Integer	PrimaryKey
   TableName	Text

    

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     Resume 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 set the command button's OnClick property to:

   =acbClearData(  )

   If you'd rather reset your data from a procedure, add the following line to your procedure:

   Call acbClearData

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.