CODEWITHSUNDEEP
sqlms-accesssql-delete
Zerround
Zerround

Reputation: 31

How to delete all record on all table with SQL (1 time)

I have 14 tables in 1 file of ms-access.

I want to delete all record on these table on one time.

I try with 

DELETE FROM table1
DELETE FROM table2
DELETE FROM table3
...
DELETE FROM table12
DELETE FROM table13
DELETE FROM table14

and run it.

But it cannot run.

How to do it?

Upvotes: 1

Views: 7424

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

You can only run one statement at a time in MS Access SQL, however, you can use MS Access VBA:

Sub DeleteTables()
    CurrentDB.Execute "DELETE FROM Table1", dbFailOnerror
    CurrentDB.Execute "DELETE FROM Table12", dbFailOnerror
End Sub

Or better

astables = Split("table1,table2,table3", ",")

For Each tbl In astables
    CurrentDb.Execute "DELETE FROM " & tbl, dbFailOnError
Next

You will have to make sure that you delete in a particular order if any of the tables have relationships to the others.

If you want to delete from all tables and the order does not matter, you can use the TableDefs collection, being careful not to delete from system tables. It is quite a dangerous option:

For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "Msys" And Left(tdf.Name, 1) <> "~" Then
        CurrentDb.Execute "DELETE FROM " & tdf.Name, dbFailOnError
    End If
Next

Upvotes: 2

Related Questions