deleting all entries in MS access

[slimt_slimt]

hi,

i'm building a MS access DataBase for my customer. in between i'm always testing if it works fine (VB code, DB connections, etc.). But at the end, i would like to have DB empty of those test entries.

what is the best and savest way to delete these entries (so non of the traces are left behind)?

thank you.

 

[ColinEssex]

If the relationships are right, deleting from the main table will cascade delete to the other tables.

Col

 

[ghudson]

Just create a function to run a SQL and delete all the data in your tables and run it when needed.

CurrentDb().Execute "DELETE * FROM YourTable1"
CurrentDb().Execute "DELETE * FROM YourTable2"
CurrentDb().Execute "DELETE * FROM YourTable3"

 

[WindSailor]

ghudson,

Another one I have used which basically works and gives you a prompt for deleting records and exits nicely if the Option "No" is chosen:

On Error Resume Next
DoCmd.RunSQL "Delete * From YourTableName1"

But this will not compile... why? ("Argument not Optional" is the error it is throwing on "RunSQL".)

On Error Resume Next
Dim SQL As String
SQL = "DELETE * FROM YourTableName1"
DoCmd.RunSQL

---------- Edit-----------

It is because I should have had:

DoCmd.RunSQL SQL

<G>

 

[ghudson]

That the advantage of using the CurrentDb().Execute "DELETE * FROM YourTable1" is that it does not prompt or give any warnings and there are times when a programmer needs that simplicity.

 

[slimt_slimt]

thanks a lot... i 'll stick to the easiest and fastes was creating a function in SQL.

best,t.

 

[slimt_slimt]

autonumber

another thing....when deleting all the records...

autonumber indexes doesn't reset!!

tnx

 

[jamesWP]

Just compact the database, make sure 'Compact on Close' is checked on the "Tools > Options... > General" tab.

It will then reset all autonumbers on exit, aswell as clearing up the other mess.