I have 2 tables:
As you can see, while RunSQL and OpenQuery start off simple, they end up causing you a lot of extra work. The OpenQuery command is really designed for displaying a SELECT or CROSSTAB query for the user to browse. For Action queries, use the Execute method to run saved queries and SQL strings:
Dim dbs As DAO.Database Dim lngRowsAffected As Long Dim lngRowsDeleted As Long Set dbs = CurrentDb ' Execute runs both saved queries and SQL strings dbs.Execute cstrQueryName, dbFailOnError ' Get the number of rows affected by the Action query. You can ' display this to the user, store it in a table, or trigger an ' action if an unexpected # (e.g. 0 rows when you expect >0) lngRowsAffected = dbs.RecordsAffected dbs.Execute "DELETE FROM tblMyTable WHERE Bad", dbFailOnError lngRowsDeleted = dbs.RecordsAffected
You can avoid using SetWarnings because Execute doesn’t show warnings. As an added benefit, you can return the # of rows affected by the most recent action query. You can show this value to users, store it in a table, or use it to check for an unexpected result (e.g. 0 rows affected when you expect > 0).