Access VBA: Error 2501 - The OpenQuery action was cancelled

Question

Let me preface all of this by saying that this issue only began last week, the same day that my computer underwent a mandatory update from corporate IT. The timing on this makes me suspect that the update is somehow responsible for this behavior, but I don't know enough to definitively prove it, or to take steps to work around it if my theory is correct. The theory is also somewhat flimsy, as my secondary computer must have had the same update, but does not exhibit this issue.

As the title suggests, I'm having problems with a recurring error in an Access database I recently built. I'm dealing with enormous data sets (some approximately 1.9 million lines in length, with dozens of associated columns). To manage this and avoid overloading any particular database with unnecessary temp tables, I've structured these databases as several independent databases that report to an 'Assembly' database that uses linked tables to create a final report that can then be distributed to end users. A final 'Master' database holds all of the scripting and allows me (theoretically) to run everything with a few button clicks.

Here's some sample code:

Public Sub Import_Click()
On Error Resume Next
  DoCmd.SetWarnings False

  SysCmd acSysCmdSetStatus, "Importing Pipeline... Please be patient."
  ImportPipeline
  SysCmd acSysCmdSetStatus, "Importing Relationships... Please be patient."
  ImportRelationships
  SysCmd acSysCmdSetStatus, "Importing Coverage... Please be patient."
  ImportCoverage
  SysCmd acSysCmdSetStatus, "Importing Digital... Please be patient."
  ImportDigital
  SysCmd acSysCmdSetStatus, "Importing Orders... Please be patient."
  ImportOrders
  SysCmd acSysCmdSetStatus, "Importing Activations... Please be patient."
  ImportActivations

  DoCmd.SetWarnings True
  SysCmd acSysCmdClearStatus

  Beep
  MsgBox ("Import Complete!")

End Sub

Within each of these 'Import' subroutines, you'll find specific code--below is a sample subroutine that is the simplest (fewest potential points of failure). Note that appAccess is a global object that I use in each of these subroutines:

Sub ImportCoverage()
On Error GoTo ErrorImp

  DeleteCoverage

  Set appAccess = CreateObject("Access.Application")
  appAccess.OpenCurrentDatabase Application.CurrentProject.Path & "\Coverage.accdb"

  appAccess.DoCmd.TransferText acImportDelim, "LSTCoverage Import Specification", "Coverage", DataFilesFolderLocation & "LSTCoverage.csv", True, ""
  appAccess.DoCmd.OpenQuery "0000 - Coverage - Add Key", acViewNormal, acEdit
  appAccess.DoCmd.OpenQuery "0001 - Coverage - Clean", acViewNormal, acEdit
  appAccess.CurrentDb.Execute "CREATE UNIQUE INDEX CovIndex ON Coverage (OppIDKey) WITH PRIMARY"

  appAccess.DoCmd.Quit acQuitSaveNone
  Set appAccess = Nothing

ExitImp:
Exit Sub

ErrorImp:
  appAccess.DoCmd.Quit acQuitSaveNone
  Set appAccess = Nothing
  DoCmd.SetWarnings True
  SysCmd acSysCmdClearStatus
  MsgBox "Coverage Incomplete! Error encountered." & vbCrLf & "Error" & Str(Err.Number) & " generated by " & Err.Source & vbCrLf & Err.Description, vbInformation, "Error"

  Resume ExitImp

End Sub

I've stepped through this code several times, and it will always fail on this line:

  appAccess.DoCmd.OpenQuery "0000 - Coverage - Add Key", acViewNormal, acEdit

The database is successfully and correctly connecting, opening, deleting old tables (in the DeleteCoverage subroutine), disconnecting, compacting, reconnecting, opening, importing a new table, and then fails as soon as I attempt to run query 0000, which is a simple Update Query. (This failure happens regardless of whatever specific queries I attempt--all queries will fail [Only on this machine and only external calls from one database into another. A query called directly from the database it's found in seems to work as normal.])

This query can be run manually 100% of the time--it is not an issue with the query fundamentally being broken. Furthermore, if I transfer these databases and source files to another computer, these OpenQuery commands will work perfectly!

The simplest solution, then, is to simply use this backup computer to run the report, and I'm doing so--however, that solution is problematic in its own way, as it is not quite as powerful as my primary machine, and is more prone to hitting the 'System Resource Exceeded' errors when handling data sets this large.

Can anyone shed any light here? I've done a lot of combing through StackExchange and other sites looking for similar issues, and although I've found a few, most seem to resolve like this one, with the original poster eventually giving up on solving the error.

I appreciate any guidance you can offer!

ETA: By request, here's the SQL of the "0000 - Coverage - Add Key" query, which also fails when attempted as a RunSQL statement. This is a standard Update query that we use frequently across multiple databases for the purposes of generating case-insensitive unique IDs for our records (as Salesforce.com generates case-sensitive IDs, but Excel treats data insensitively in a Pivot Table).

strSQL = "UPDATE Coverage 
  SET Coverage.OppIDKey = Asc(Mid([Opportunity ID],1,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],2,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],3,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],4,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],5,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],6,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],7,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],8,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],9,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],10,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],11,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],12,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],13,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],14,1)) & ' - ' 
    & Asc(Mid([Opportunity ID],15,1)) 
  WHERE (((Coverage.LTN) Is Not Null));"

Answer 1

I just had the very same experience - system was updated, vba stops on a openquery command for a database opened in vba ...

    Public Function fOpenAccess(strPathToFile As String) As Boolean

' Create new instance of Microsoft Access.
    Set appAccess = CreateObject("Access.Application")
' Open database in Microsoft Access window.
    With appAccess
        lCounter = lCounter + 1: Call fProgress(lCounter)
        .OpenCurrentDatabase strPathToFile
        '.Visible = True
    ' Open some queries.
        lCounter = lCounter + 1: Call fProgress(lCounter)
        .DoCmd.OpenQuery "qryWIPImport1"
        lCounter = lCounter + 1: Call fProgress(lCounter)
        .DoCmd.OpenQuery "qryWIPImport2"
        lCounter = lCounter + 1: Call fProgress(lCounter)
        .DoCmd.OpenQuery "qryWIPImportDelete"
    ' Close the database
        lCounter = lCounter + 1: Call fProgress(lCounter)
        .DoCmd.Quit acQuitSaveAll
    End With
End Function

After some troubleshooting I got to the step of opening the called database to test things directly from there. The first thing I got was the "Enable Content" button. I decided to click the button, close the db and try my code again and it worked as usual.

Moral of the story... Enable Content on your external files or disable this security warning if your code connects to external office files.

Answer 2

If 0001 works, why not 0000? (Are you sure only 0000 is broken? Step through the code and manually move the cursor so as to skip 0000.)

You have many non-broken cases, only one broken one. Because you understand the value of that, you will prevail. Isolate the problem by making a new module for testing. Reduce the code to the minimum that will raise the error. Possible remedies:

1. Decompile and recompile the database.
2. Make a new version starting with empty database, then add in objects and code; this evades a subtle corruption, but you may need to take it in stages to find the bad part.
3. Check available libraries to see if a library reference is off.
4. Add DoEvents to keep the operating system from getting tangled.
5. Replace the query with a recordset-based approach.
6. New: Ensure you can open and view records in the table which is updated by your query. If not, fix the table link (try just deleting and creating a fresh one using Linked Table Manager).

---

I'm curious what values for Opportunity_ID look like. 15 Asc(Mid()) outputs may be all right, but from a performance point of view it seems suspect.

Assuming you can view records from Coverage, create simple queries (not in VBA) that build up until a problem shows up:

1. SELECT [Opportunity ID] FROM Coverage;
2. SELECT [Opportunity ID], Mid([Opportunity ID],1,1) FROM Coverage;
3. ... etc ...