title: Access VBA: Error 2501 - The OpenQuery action was cancelled
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Here's some sample code:</p>
  
  <pre><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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Sub ImportCoverage()
  On Error GoTo ErrorImp
  
    DeleteCoverage
  
    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase Application.CurrentProject.Path &amp; "\Coverage.accdb"
  
    appAccess.DoCmd.TransferText acImportDelim, "LSTCoverage Import Specification", "Coverage", DataFilesFolderLocation &amp; "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." &amp; vbCrLf &amp; "Error" &amp; Str(Err.Number) &amp; " generated by " &amp; Err.Source &amp; vbCrLf &amp; Err.Description, vbInformation, "Error"
  
    Resume ExitImp
  
  End Sub
  </code></pre>
  
  <p>I've stepped through this code several times, and it will always fail on this line:</p>
  
  <pre><code>  appAccess.DoCmd.OpenQuery "0000 - Coverage - Add Key", acViewNormal, acEdit
  </code></pre>
  
  <p>The database is <strong>successfully and correctly</strong> 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.])</p>
  
  <p>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!</p>
  
  <p>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.</p>
  
  <p>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 <a href="https://stackoverflow.com/questions/14371022/vb-net-application-using-accessdb-to-run-maketable-query-wont-work-on-other-mach">like this one</a>, with the original poster eventually giving up on solving the error.</p>
  
  <p>I appreciate any guidance you can offer!</p>
  
  <p><strong><em>ETA:</em></strong> 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).</p>
  
  <pre><code>strSQL = "UPDATE Coverage 
    SET Coverage.OppIDKey = Asc(Mid([Opportunity ID],1,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],2,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],3,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],4,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],5,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],6,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],7,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],8,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],9,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],10,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],11,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],12,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],13,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],14,1)) &amp; ' - ' 
      &amp; Asc(Mid([Opportunity ID],15,1)) 
    WHERE (((Coverage.LTN) Is Not Null));"
  </code></pre>