Bulk DELETE or TRUNCATE query

  • Thread starter Thread starter ricky
  • Start date Start date
R

ricky

Hi

I have several tables that I wish to clear down and update with new data.
Is it possible to write one query, which clears down/empties all the
required tables?

I have tried writing multiple DELETE statements, but that does not seem to
work?

Kind Regards

Ricky
 
ricky said:
I have several tables that I wish to clear down and update with new data.
Is it possible to write one query, which clears down/empties all the
required tables?

I have tried writing multiple DELETE statements, but that does not seem to
work?
Click to expand...


You do need a separate delete for each table. Either you
have something wrong in your queries or you are executing
them in the wrong order. If two (or more) tables have
referential integrity set (and they should), then you must
delete the child records before you delete the parent
records.
 
ricky said:
Hi

I have several tables that I wish to clear down and update with new
data. Is it possible to write one query, which clears down/empties
all the required tables?

I have tried writing multiple DELETE statements, but that does not
seem to work?

Kind Regards

Ricky
Click to expand...

You cannot have multiple statements in an Access query, but you can easily call
a series of delete query statements from VBA code...

Dim db as Database
Set db = CurrentDB

With db
.Execute "DELETE * FROM Table1", dbFailOnError
..Execute "DELETE * FROM Table1", dbFailOnError
..Execute "DELETE * FROM Table1", dbFailOnError
..Execute "DELETE * FROM Table1", dbFailOnError
..Execute "DELETE * FROM Table1", dbFailOnError
End With
 
Rick said:
You cannot have multiple statements in an Access query, but you can
easily call a series of delete query statements from VBA code...

Dim db as Database
Set db = CurrentDB

With db
.Execute "DELETE * FROM Table1", dbFailOnError
.Execute "DELETE * FROM Table1", dbFailOnError
.Execute "DELETE * FROM Table1", dbFailOnError
.Execute "DELETE * FROM Table1", dbFailOnError
.Execute "DELETE * FROM Table1", dbFailOnError
End With
Click to expand...

Accidentally hit <Ctl-Enter> while copying the first line. Obviously table name
would be different in each of the above statements.
 
Back
Top