Stupid Access Tricks #1: Running queries in parallel
Warning: I ran into some trouble when I used this, in which I lost some data. I believe it was due to Access database locking, but I did not have time to investigate completely. I’m keeping it here because I [cautiously] think it can be useful, with modifications to ensure that nothing gets lost upon error. As always, use at your own risk.
Sometimes you have a long-running query, that can be broken up into multiple results. Let’s say you have a query, “Append Records to Table” that can be split up by using a range, based on a column called ID — which is a long integer.
For the below, we assume that all files are stored in the C:PathToData folder.
In your source query, “Append Records to Table”, you can add the following parameters to your WHERE clause:
WHERE ... AND (ID > START_ID AND ID <= END_ID)
This assumes that START_ID and END_ID are not existing fields in a table in the query.
Now Access will recognize START_ID and END_ID as parameters into the query.
We can now create an external file, C:PathToDataRun Parallel.vbs, which looks like this:
OutPath = "C:PathToData" Sub Run_Query(QueryDef, Start_ID, End_ID) Dim dbEngine Dim db Dim qd Set dbEngine = CreateObject("DAO.DBEngine.36") 'DAO.DBEngine.120 for Access 2007, 2010, 2013 Set db = DBEngine.OpenDatabase(OutPath & "Source Database.mdb") Set qd = db.QueryDefs(QueryDef) qd.Parameters("START_ID")=Start_ID qd.Parameters("END_ID")=End_ID qd.Execute db.Close End Sub If WScript.Arguments.Count <> 2 Then MsgBox "Pass 2 args." Else '"Append Records to Table" is the query that takes 2 parameters: a Start_ID and and End_ID. 'Run_Query fills in these parameters, and executes the query. Run_Query "Append Records to Table", WScript.Arguments(0), WScript.Arguments(1) Set txt = CreateObject("Scripting.FileSystemObject").OpenTextFile(OutPath & "Log.txt", 8, True) '8=ForAppending, True=Create file txt.WriteLine "Job Complete: Range " & WScript.Arguments(0) & " - " & WScript.Arguments(1) End If
Finally, we need a subroutine in the Access database (where your source query lives) to shell out to the script. The below Access subroutine will run the script 20 times in parallel, with 20 different ranges.
Sub Run_Parallel() PrevID = 0 For ID=1 To 200000 Step 10000 '200000 is the theoretical max ID in the source table Shell "wscript.exe ""C:PathToDataRun Parallel.vbs"" " & PrevID & " " & ID + 9999 PrevID = ID + 9999 Next End Sub
Ideally, the script should have some sort of locking mechanism to prevent more than N scripts running at a time (N being the # of processors you have, or 2*Number of processors). However, that’s beyond the scope of this article.
Now, when you run Run_Parallel, it will execute the external script 20 times, with different values of START_ID and END_ID, effectively breaking up your query into 20 parts.
You should compile your Access VBA script before running (Debug->Compile in the Visual Basic editor), or it could lead to some errors along the lines of “the database has been locked by user ‘Admin'”.
One other caveat is that your queries won’t run if they contain VBA code: the query needs to be straight SQL. One common example of this is if your SQL contains the Nz() operator. Nz() is actually a VBA function and not a Jet SQL function. It can effectively be replaced by: IIf(Var IS NULL, ‘Null Value’, Var), which will work in Jet SQL. In order to make queries containing VBA run, you would have to open a separate instance of Access instead of just creating a DBEngine for each run.