Using VBA to Automate Delete Queries....

[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.

 

[jhall156]

You might use DAO and a querydef object whose SQL property you build programmatically then invoke Execute. JHall

 

[thevillageinn]

There's also...

DoCmd.RunSQL "Delete * From CSHJNL", False

some people may argue it's not quite as effective as some other command ( I forget what it is, there was a thread on it in this forum last week ) but it works for my purposes.

Hope that helps a bit...
-Dan