Access Cookbook

Clean Test Data out of a Database When You’re Ready to Ship It

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?

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