Delete all the records from all the Table

[Irshad Alam]

I would like to request for a code solution for the below action to be done
on a button click :

I want to delete all the records of all the tables in the database on a
button click.

Generally I deliver the database mdb to different people but after making
all the tables blank, i have to work manually. the code will save my huge
time.

Thanking you all for the support you people provide us.

Best regards

Irshad

 

[Paolo]

Hi Irshad Alam,
I think this code would do the trick. Just copy and paste it in the on click
event of your button.

Dim tbl As DAO.tabledef

DoCmd.SetWarnings False
For Each tbl In CurrentDb.tabledefs
With tbl
If (tbl.Attributes And (dbSystemObject Or dbHiddenObject)) = 0
Then
DoCmd.RunSQL "delete * from " & tbl.Name
End If
End With
Next tbl
DoCmd.SetWarnings True
MsgBox "done"

HTH Paolo

 

[Irshad Alam]

Thank you so much, it worked perfect.

Regards

Irshad

 

[Allen Browne]

If you have created relationships between your table with Referential
Integrity turned on (as you should), then this solution won't work
correctly.

Say Table3 has a foreign key field that refers back to Table1. When the code
tries to delete the records from Table1, JET cannot delete those records,
because they are used by Table3. Since you turned off SetWarnings, you won't
get any notification: it will silently fail, i.e. some records will be left
in some tables without telling you.

For this reason, I would suggest:
a) Use Execute rather than RunSQL.
Explanation of why in this article:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

b) Instead of looping through all the TableDefs, you will need to specify
the order for the tables (one per line.)

It may be possible to write code that examines all the relationships and
tries to figure out the order for deleting records. However, you can't
guarantee that this solution will work, as JET does permit cyclic relations
(e.g. where table A is related to B, and B to C, and C to A.) Further, I
find that I do want to leave some records in some tables (small lookups,
configuration data, zip codes, etc.) It's not something you do often, so
hard-coding the tables, one-per-line is probably the simplest approach.

 

[John W. Vinson]

I would like to request for a code solution for the below action to be done
on a button click :

I want to delete all the records of all the tables in the database on a
button click.

Generally I deliver the database mdb to different people but after making
all the tables blank, i have to work manually. the code will save my huge
time.

Thanking you all for the support you people provide us.

Best regards

Irshad

I'd actually suggest a different approach: create a new, empty database, and
use File... Get External Data... Import to import all the tables (and other
objects, if you don't have the database split). You can specify "design mode
only" when importing tables, so it will import the structure of the table but
no data.