Optimal way to use Database.Execute method from VBA against MSSQL linked through ODBC

Question

The Database.Execute DAO method has so many parameters and I'm not sure what would be the optimal way (fast, efficient and avoiding locks) to execute update and insert statements againts an MSSQL database from VBA code (MS Access). I'm accessing the MSSQL tables through ODBC.

Database.Execute method (DAO)

Now I'm using it as follows:

CurrentDb.Execute "SQL statement comes here"

or

CurrentDb.Execute "SQL statement comes here", dbSeeChanges

I have two scenarios:

• I want to update or insert data into business tables (customers, invoices etc.) - here I use the version with dbSeeChanges - because multiple users may read and write these tables at the same time
• I insert records into a log table - I use the plain version here - only inserts happen in this case, there's no read from these tables

But I'm not sure if this is really the best way to use it.

Answer 1

what you use now is fine...have used myself in very large apps with SQL Server in the back end....