Formatting

[Dave31]

Hi
I have a database with multiple tables all linked together in various ways. I would like a master reset button that will delete every record in every table as well as resetting the auto number(s) back down to zero again. Can this be done and if so, how?
Thanks in advance.

Dave

 

[gemma-the-husky]

you nned to determine the order of tables to delete, as you need to get rid of the many sides of the relationship first. (although maybe cascading deletes will do this for you. )

sql is just "delete * from tablename"

then compact database should reset the autonumbers.

 

[Dave31]

thanks for the reply, although i have a couple more questions. I created a query to delete the records in a table (just one table for the moment as a tester). As i want a button to allow me to activate this query, i created a button (using the wizard) and when i select to 'run a query', the delete query is not there to be selected, any ideas?

Also, what do you mean by compacting the database? and how do you do that?

 

[jeremypaule]

tools -> database utilities -> compact and repair

 

[gemma-the-husky]

if you created a stored query, then when you drop the button on the page, the wizard will let you pick the query, and code it automaticllay.

otherwise in the buttons onclick event you need

docmd.openquery "myqueryname"

 

[Dave31]

Nearly there, although, im having trouble with my query for deleting all records in all my tables:

DELETE [defectTable.*], [meetingTable.*], [cdsidTable.*], 
[participantTable.*], [phaseTable.*], [productTable.*], 
[programTable.*], [workProductTable.*]
FROM defectTable, meetingTable, cdsidTable, participantTable, 
phaseTable, productTable, programTable, workProductTable;

When i run the query, theres an runtime error stating 'Specify the table containing the records you want to delete'.
Can anyone help?

 

[gemma-the-husky]

you need to process each table, one at a time

 

[Dave31]

How can i do that?
Sorry, im still fairly new to Access.

 

[gemma-the-husky]

docmd.runsql "DELETE * from defecttable"
docmd.runsql "delete * from meetingTable"

etc

 

[Dave31]

Ah, fantastic, thanks