How do I empty a table?

[Steve Hayes]

I want to develop a database and test it with test data.

How do I empty the database of the test data when I've finished testing and
want to enter real data?

In Paradox it was easy and obvious, but in Access I haven't been able to find
it.

 

[Jeanette Cunningham]

You write a series of queries that delete data from each table.
Delete the records in any child tables before you delete the records in any
parent tables.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[BruceM]

You could run a delete query:

DELETE * FROM YourTable

If you use cascading deletes when you set up the relationships you can run
the query on the main table in the relationship, and the related records
will be deleted also.

I have been known to open the tables, and select and delete all test data.
In this case I think it's OK for the developer to work directly with the
tables.

When you are done, run compact and repair (after backing up first). This
will reset the autonumber to 1 (I can't say for sure how this works in
Access 2007, though). In general, compact and repair is a good idea before
deploying the database.

 

[gls858]

I want to develop a database and test it with test data.

How do I empty the database of the test data when I've finished testing and
want to enter real data?

In Paradox it was easy and obvious, but in Access I haven't been able to find
it.

Another method would be to create a blank database then import your
tables into it. When you select the tables to import you have an option
to bring over just the table definitions.

gls858

 

[Steve Hayes]

Another method would be to create a blank database then import your
tables into it. When you select the tables to import you have an option
to bring over just the table definitions.

Would that bring over everything except the data -- saved queries, etc?

 

[John W. Vinson]

Would that bring over everything except the data -- saved queries, etc?

Sure, if you import the saved queries and the etc. (forms, reports, macros,
modules).

You can import selectively. Just use File... Get External Data... Import,
select the database, and choose what you want to import.

 

[Armen Stein]

Sure, if you import the saved queries and the etc. (forms, reports, macros,
modules).

You can import selectively. Just use File... Get External Data... Import,
select the database, and choose what you want to import.

But this would imply that you haven't split your database. Here are a
bunch of reasons to do so, plus some links to help you with it:

http://allenbrowne.com/ser-01.html

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[KenSheridan via AccessMonster.com]

A quick and dirty way is to open each table in datasheet view, press Ctrl-A
to select all rows and then press the Delete key. You'll be prompted to
confirm the deletions. Start with the referencing tables, i.e. those on the
many side of one-to-many relationships. Assuming you've enforced referential
integrity you won't be able to delete any rows from a referenced (one-side)
table while matching rows exist in referenced tables unless you've also
enforced cascade deletes, which is sometimes appropriate, sometimes not,
depending on what the tables are modelling.

Ken Sheridan
Stafford, England