Runing Access Queries
My favorite method for executing SQL, be it saved queries or raw SQL, is the Execute method of the Database object. You can execute a statement with a single line, like this:
[code]
CurrentDb.Execute “UPDATE titles SET price = price * 1.10″
[/code]
In this case, CurrentDB references the currently open database. Alternatively, you can create and load a Database object variable, which will allow you to accomplish more with this method. After setting the variable, you can execute a query, supply additional options and even read the number of rows affected. While this takes a few more lines, it is by no means verbose:
[code]
Dim dbs As DAO.Database, sql as String, iCount as Integer
Set dbs = CurrentDb
sql = “DELETE * FROM stores WHERE state=’WY’”
‘(not that there’s anything wrong with Wyoming!)
dbs.Execute sql, dbFailOnError
iCount = dbs.RecordsAffected
[/code]
