Removing data recursively

[Guest]

On an Access database, I would like to recursively remove all data for each
table. Some of the tables are linked with relationships.

If it possible to write a procedure to remove all data for each table while
ignoring their established relationship? If so, would I go about it?

 

[Allen Browne]

You would know which tables depend on which other tables, so you could write
a procedure that executes a DELETE query on each table in turn, working up
the tree so that each table has no children when you delete the records.

Alternatively, you could create cascading relationships. Then when you
delete the highest level tables, their child records are deleted in the
related tables as well.

If you have circular relations (where some parents are their own children or
grandchildren), you may need to programmatically break the relations, delete
the records, and then create the relations again.

Or, if the goal is to get an identical data structure without any data, you
could create a new blank database, and import everything using File |
Import. Under the Options button in the Import dialog, one of the choices is
Structure Only.

One of those should get you going.

 

[Guest]

Thanks for your reply. Does the last option "import everything" includes all
the established relationships.

 

[Allen Browne]

Yep: that option is checked by default when you import.