How do I get an empty database from a filled database in Access?

[Arlo]

I have created a database using Access, and filled it with data to test it.
Now I want to empty it, and prepare it for distribution. What is the best
way to do this?

 

[Rick Brandt]

I have created a database using Access, and filled it with data to
test it. Now I want to empty it, and prepare it for distribution.
What is the best way to do this?

One way is to import everything into a new blank file. When doing this there is
an option to import only table definitions (no data).

 

[TedMi]

By "import", Rick means to copy each table out of the existinhg .mdb and
paste it into a new blank .mdb file. Do not use File > Get External Data >
Import.
However, if you do this, you will need to re-establish your relationships
and referential integrity in the new database. An easier way is to run this
query on every table in your test db:
DELETE FROM [NameOfTable]
You could automate this with VB code if there are many many tables.
Make a backup of the test db first!

 

[Rick Brandt]

By "import", Rick means to copy each table out of the existinhg .mdb
and paste it into a new blank .mdb file. Do not use File > Get
External Data > Import.
However, if you do this, you will need to re-establish your
relationships and referential integrity in the new database. An
easier way is to run this query on every table in your test db:
DELETE FROM [NameOfTable]
You could automate this with VB code if there are many many tables.
Make a backup of the test db first!

No, I meant File - Get External Data -Import from a new blank file. If you do
this you don't lose your relationships if you take the option to include them.

Why would you think that one should not use the import option?

 

[Brian]

Hey, Rick. Just a followup question here.

I the past, I have used import, but having to remember to recreate my
Startup options got the best of me, so I eventually just created a form that
has a (password-protected) button that runs a series of DoCmd.RunSQL "DELETE
* FROM WhateverTable" statements in the correct order to avoid referential
integrity violiations. Then I run a compact/repair to reset AutoNumber fields
back to 1, decompile, recompile, and run a final compact/repair.

Two questions:
1. Is there a down side to the above approach?
2. Where are the Startup option stored? If they are DB properties, then I
could just query the Properties collection to update the new DB to match the
original.

By "import", Rick means to copy each table out of the existinhg .mdb
and paste it into a new blank .mdb file. Do not use File > Get
External Data > Import.
However, if you do this, you will need to re-establish your
relationships and referential integrity in the new database. An
easier way is to run this query on every table in your test db:
DELETE FROM [NameOfTable]
You could automate this with VB code if there are many many tables.
Make a backup of the test db first!

No, I meant File - Get External Data -Import from a new blank file. If you do
this you don't lose your relationships if you take the option to include them.

Why would you think that one should not use the import option?

 

[Rick Brandt]

Hey, Rick. Just a followup question here.

I the past, I have used import, but having to remember to recreate my
Startup options got the best of me, so I eventually just created a
form that has a (password-protected) button that runs a series of
DoCmd.RunSQL "DELETE * FROM WhateverTable" statements in the correct
order to avoid referential integrity violiations. Then I run a
compact/repair to reset AutoNumber fields back to 1, decompile,
recompile, and run a final compact/repair.

Two questions:
1. Is there a down side to the above approach?

No big ones that I know of. It could be a maintenance problem if you frequently
add new tables.

2. Where are the Startup option stored? If they are DB properties,
then I could just query the Properties collection to update the new
DB to match the original.

Startup options and security are two things that have to be redone when
importing objects into a new file. I see no problem with the former and don't
use the latter.

You could have a code routine that sets all of the startup options and then just
run that after doing the import.