Emptying Database

[Thomas M]

Access 2003

I have a small database that I developed in Access 2000. I have
recently updated it to Access 2003 and have been working on a few
enhancements. The main table in the database now contains 118 rows of
test data. Once I have finished developing this database I would like
to empty out the database and set the autonumber field to start over at
row 1.

Questions:

1) Is that a good thing to do, or will it somehow damage the database?

2) If it won't damage the database, then how can I empty the database
and reset the autonumber field to start at 1?

3) If it will damage the database, then what is my best option for
getting rid of the test data?

--Tom

 

[Allen Browne]

A1. No problem. It is normal to remove the test data.

A2. Your database will have several one-to-many relationships, hopefully
with Referential Integrity enforced. You will need to delete the data from
the child tables before you can delete from the main tables. (That's not
necessary if you enabled cascading deletes, but you will only want to do
that in limited cases.)

For just a few records, open the table in design view.
Ctrl+A selects all records.
Del deletes all.
Repeat for the next table.
Once all data is deleted, compact the database to reset the autonumbers:
Tools | Database Utilities | Compact

Then make a copy of the empty database, and test it. It is actually quite
common to find code that fails when there is no data, e.g. if it attempts to
MoveNext.

 

[Thomas M]

Thanks for the information. I have a couple of more features to add and
test, so it will be a while before I actually get to the point of
deleting the test data. But at least I'll know how to do it.

--Tom