Automate Delete Queries using VBA or macro

[mrussell71]

I have two tables tblIndex and tblParts in this database provided by an outside contractor. I needed to delete corresponding records out of both tables. Unfortuneately, the tables are not linked and cannot be linked.

To work around this I created a select query using the tblParts as the data source that looks for certain records that meet a criteria. I then created a Delete Query with the tblIndex and the above select query as the data sources and linked the associated fields to find all the records from tblIndex that need to be deleted. I then created another Delete Query to delete the records from the tblParts.

I have thirteen cases where records need to be deleted. Therefore I have 26 delete queries that need to be run to remove the unwanted data.

How do I use Access to automate the Delete Process? I'm familiar with docmd.openquery but this command doesn't like delete queries.

Any help would be greatly appreciated.

 

[mdthornton]

You should use:

CurrentDB.execute "qMyDelete"

You can also pass raw SQL so depending on how complicated your joins etc are you might be able to build your SQL in VB. Eg,

mySQL1 = "delete from tblIndex where x = y"
mySQL2 = "delete from tblParts where x = y"
CurrentDb.execute "mySQL1"
CurrentDb.execute "mySQL2"

You can also check the results using RecordsAffected property, eg:

CurrentDb.execute "mySQL1"
MsgBox "Rows deleted: " + Cstr(CurrentDB.RecordsAffected)
Good Luck,
Mike

 

[mrussell71]

Thanks Mike that did the trick.