Stupid Access Tricks #1: Running queries in parallel

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.