Clean all "data" from a database

  • Thread starter Thread starter JR Hester
  • Start date Start date
J

JR Hester

Access 2007 on Win XP

I have an existing database that I want to use for a new function. I do want
to start out with empty tables after the demonstration and training phase.
Therefore I am looking for a way to empty all data records from all tables.

I have read and understand the process of creating a new database and
exporting the tables as structure or definition only. My question is in
Access 2007 is it possible to execute this procedure on more than one table,
query,form, or report at a time? I have 10 tables, 20 queries, 22 forms, and
32 reports. I have attemptted to select multiple items, but then the option
to export to access database is not available. Is there an alternative to
executing the export function 94 individual times? I would hope there is!

Copying and renaming the database, then just running delete queries will not
work as the boss can't accept the first record he enters having an ID of 9815.

Thanks for your ideas.
 
Access 2007 on Win XP

I have an existing database that I want to use for a new function. I do want
to start out with empty tables after the demonstration and training phase.
Therefore I am looking for a way to empty all data records from all tables.

I have read and understand the process of creating a new database and
exporting the tables as structure or definition only. My question is in
Access 2007 is it possible to execute this procedure on more than one table,
query,form, or report at a time? I have 10 tables, 20 queries, 22 forms, and
32 reports. I have attemptted to select multiple items, but then the option
to export to access database is not available. Is there an alternative to
executing the export function 94 individual times? I would hope there is!

Copying and renaming the database, then just running delete queries will not
work as the boss can't accept the first record he enters having an ID of 9815.

Thanks for your ideas.
Click to expand...

Try creating a new, empty database and *importing* everything. I don't have
2007 open at the moment but I think it will let you (at least) select all the
tables and import them, all the forms, etc.

If your boss expects Autonumbers to start at 1 and be sequential and gapless,
he'll be disappointed. That is NOT the function of an autonumber; they will
always have gaps and can become random. Autonumbers should generally be kept
"under the hood" and not exposed to user view.
 
Hi JR,

You should only need to worry about clearing data out from the 10 tables, in
a copy of your database, unless you have other data embedded in label
captions or elsewhere. You can create a series of delete queries in VBA code
to accomplish this goal, or you can create actual saved delete queries and
run them sequentially via code or a macro. If you have relationships with
enforced referential integrity (and you should have this), then you will need
to delete records from child tables before deleting records from parent
tables. This could all be initiated with the click of a command button, or
issuing a command via the Immediate Window.

To get an autonumber to start back at 1, you need to delete all records from
the table in question and then do a compact and repair. In Access 2007, click
the button thingy in the upper left corner, then select Manage, followed by
Compact and repair. I recommend hiding (set visible property to No) text
boxes on forms and reports that are bound to autonumber data type. A user,
including your boss, should never see an autonumber value displayed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
ID of 9815

If that field is an autonumber, doing a compact and repair should reset it
back to 1.
 
Back
Top