Automated Delete Query

[Syed Rizvi]

I have a database which needs to be clean up on a periodic basis using a
Delete query. All query is going to do is to delete some records from number
of tables based on specified criteria.

I would like to make this process as automated as possible. One of the
option I was thinking to use a batch file. So user even who do not know
anything about MSAccess can just double to run and database will update. But
I am not sure if it can be possible. Could some please advise. Any help or
tips would be greatly appreciated.

Regards
Syed Rizvi

 

[Allen Browne]

The core idea would be to build the criteria string, and then use it in the
Delete query string. This kind of thing:

Dim db As DAO.Database
Dim strSql As String
Dim strWhere As String

strWhere = "(SaleDate < #1/1/2000#) AND (IsUseless = True)"
Set db = CurrentDb()

strSql = "DELETE FROM Table1 WHERE " & strWhere
db.Execute strSql, dbFailOnError

strSql = "DELETE FROM Table2 WHERE " & strWhere
db.Execute strSql, dbFailOnError

'etc

Set db = Nothing

Note that if you use referential integrity (as you should), you will need to
delete from the related tables before the primary tables (unless you have
cascading deletes, which you should use only sparingly.)

If you want to use a transaction (so you get an all-or nothing result), this
example provides an illustration and explains the pitfalls:
http://allenbrowne.com/ser-37.html