Reset tables to delete all data

[jillrein]

Can someone help please. I have built and tested my database and now need to delete all the data and reset all autonumbers. Is there an quick way to do this without having to create and run lots of delete queries?

 

[boblarson]

Can someone help please. I have built and tested my database and now need to delete all the data and reset all autonumbers. Is there an quick way to do this without having to create and run lots of delete queries?

You can open your tables and do EDIT > SELECT ALL and then hit your delete key.

As for resetting your autonumbers, once the table is empty and you run a COMPACT and REPAIR on the database it should reset them.

However, hopefully you aren't using autonumbers for anything that the user sees. It should only matter to the system what they are and it can manage it just fine. Autonumbers are used to give you a UNIQUE number but they are never guaranteed to always be in sequential order and once a record is started or deleted that autonumber is gone.

 

[jillrein]

Thanks for your help - I was rather hoping there was a simple way of creating a whole new set of blank tables - gets annoying having to delete from tables in the correct order to avoid problems with referential integrity rules but this certainly helps.

 

[The_Doc_Man]

jillrein, if you don't mis-use the keys/autonumber fields, you would never have any problem with RI anyway.

 

[boblarson]

Thanks for your help - I was rather hoping there was a simple way of creating a whole new set of blank tables - gets annoying having to delete from tables in the correct order to avoid problems with referential integrity rules but this certainly helps.

Your situation made me wonder if I could create a tool to help. So I did. This should reset all non-linked tables in your database. If you don't want one to be reset you can delete the table from the list of tables to reset. Also, you can set the deletion order if you wish by setting the order in the order field. If you do, make sure to replace all ZERO entries with something as they will fire first.

 

[Simon_MT]

What about creating a new database and importing tables - structure only. This seems a one-off task and this would do.

Alternatively, you could avoid Autonumbers altogether and use DMax("Field") + 1. I'm not sure this would be considered wise though!

Simon

 

[boblarson]

What about creating a new database and importing tables - structure only. This seems a one-off task and this would do.

Using my new tool is actually way faster.

 

[jillrein]

This is a great tool - thank you. I am thinking, if I store the delete order in a table in each database, I can then run this to reset everything without having to think about the order more than once! (as long as I exclude the delete order table from the list). I really appreciate your help.

 

[boblarson]

Glad I could help.

 

[gemma-the-husky]

i do that

have a delete table to specifiy the delete order

then its just "delete * from " & whatevertable

-------
however - its one thing clearing out data in a test environment, or while you are developing, but you shouldnt need to do it regularly


--------
bob - did you mean non-linked tables, or linked tables?

not that it matters - same principle anyway

 

[boblarson]

bob - did you mean non-linked tables, or linked tables?

not that it matters - same principle anyway

The tool does NON-linked tables (so if it is linked to Access tables then it needs to be done on the backend). If you have SQL Server (or other ODBC linked tables) it doesn't do those.

 

[cristiscu]

A very cheap alternative would be to simply use a fiverr gig:

fiverr.com/cristiscutaru/create-an-empty-copy-of-your-access-or-sqlite-database-file

I tried the tool recommended before and the idea was great, but it didn't work for my db.