How to clear records from a database

[Guest]

I have been entering "fake" data into my database while creating it and now I
am ready to enter the real data, but need to get rid of the old records. I
know I can simply delete selected records, but whenever I do that, there is
always a gap in the record ID numbers and the count if off. How do I
completely clear out records from a database to make it as if they were never
there. Anyone with experience on this, I would love the help!

 

[Guest]

Try compacting after deleting. Close and reopen.

 

[John Vinson]

I have been entering "fake" data into my database while creating it and now I
am ready to enter the real data, but need to get rid of the old records. I
know I can simply delete selected records, but whenever I do that, there is
always a gap in the record ID numbers and the count if off. How do I
completely clear out records from a database to make it as if they were never
there. Anyone with experience on this, I would love the help!

A Delete query

DELETE * FROM tablename;

will delete all records (if you have relational integrity enforced,
you'll need to run these delete queries in the right order, "many"
side tables first).

Autonumbers ARE NOT counts, and ARE NOT suitable for "record ID
numbers' which will be seen by people. An autonumber will *always*
have gaps - not only deleted records, but even if you start to enter a
record, change your mind, and hit <Esc> to cancel, you'll "use up" an
autonumber. Some developers never use autonumbers at all; most do, but
keep them "under the hood" as linking fields, concealed from the user.

If you want to count records, don't (EVER!) use the ID number to do
so. Instead, do a totals query and... count the records that are
there.

If you need a human readable sequential ID number, it's best not to
use autonumber; instead, use a Long Integer and VBA code to assign the
values.

John W. Vinson[MVP]

 

[Guest]

To clear out records and make it as if they were never there, try this:

With your database open, click on File - Get External Data - Import.
Navigate to you database (yes, the same one) in the Import Dialog Box.
Choose the Tables Tab, and hylite the table that you want to start over with.
Here's the important part - click on the Options >> Button, and select the
Import Tables Definition Only radio button. Click OK. This effectively
copies only the design, and gives you a pristine table to start over with.
(The Table Name probably has a "1" added to the old name. Delete the old
table, rename the new.) Any autonumber feature will start over. However, as
John mentioned, this doesn't prevent gaps from occuring in the future.