Deleting all data from B_E database tables. (AC2003)

  • Thread starter Thread starter bUncE
  • Start date Start date
B

bUncE

Hi all,

Im trying to write a query so that all data in all but two tables is
deleted, not the tables themselves only the data.

I keep getting these errors when im trying to build a suitable query..

COULD NOT DELETE FROM SPECIFIED TABLES
or
TOO MANY FIELDS DEFINED

With any luck i'd like to able to have this query run automatically each day
is there any way to do that or do i have to repost in vba section?

Thanks for reading!!
 
You can't do that with a single query.

You could do it with a series of Executes in VBA code, e.g.:
Dim db As DAO.Database
db.Execute "DELETE FROM Table1;", dbFailOnError
db.Execute "DELETE FROM Table2;", dbFailOnError
'etc.

However, you will need to bear in mind the way your relationships work. If
there is a one-to-many relation between Table1 and Table2, the above will
fail because the Table1 records cannot be deleted while the Table2 records
exist (unless you have set up cascading deletes.)

You may need to ensure you don't have circular cascading deletes on your
tables.
 
I'm not sure I understand the purpose of having a database if you are going
to delete all of the data every day. Can you explain why you think you need
to do this?

Dale
 
Back
Top