Search code examples
sqlvbams-accessms-access-2016

Ensure synchronous SQL query calls on a Microsoft Access database with VBA

I have a vba script that loops through several update queries performed on a table in Microsoft Access.

Should i use CurrentDb.Execute or DoCmd.RunSQL to ensure that the update queries are run sequentially and do not get ahead of each other?

Essentially,

Do Until Counter = 7

CurrentDb.Execute "sql_update1"
CurrentDb.Execute "sql_update2"
CurrentDb.Execute "sql_update3"

Counter = Counter + 1
Loop

I want to make sure that sql_update2 does not start running until sql_update1 is finished as sql_update2 relies on what sql_update1 did.

Solution

  • .. ensure that the update queries are run sequentially

    VBA is single-threaded, so it can only run the queries - and any other code - sequentially.

    You will need a call to an external process to obtain something to run asynchronously. Shell is typically used for that.