1

I realize that MSAccess/VBA is a single-threaded application. But, maybe a silly question, can I call an SQL Stored Procedure from MSAccess and let the SQL Server do the heavy lifting in parallel with other MSAccess tasks?

This is my current SP call, takes about 4 minutes (no progress indicator at all) to complete with my largest datasets (exports data from SQL, reformats to produce proper CSV format, ZIPs and puts to my FTP Site - reformatting is the longest task, export & FTP are very fast):

Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.ConnectionString = "driver={SQL Server};server=" & TempVars!my_ip & ";Trusted_Connection=no;Database=" & TempVars!my_Database & ";UID=userid;PWD=password"
cnn.CommandTimeout = 0
cnn.Open

strSQL = "ExportToCSVZip 'ITEM', 'InvId', '4892', '\\123.456.789.147\Processing\Exports\', 'ProdCIV', 'Y';"

Set rs = cnn.Execute(strSQL)

Am I locked into waiting for this long external process to complete before continuing in MSAccess?

Ken White
  • 123,280
  • 14
  • 225
  • 444
Mark Pelletier
  • 1,329
  • 2
  • 24
  • 42
  • Review https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom/can-i-run-a-sql-stored-procedure-from-vba-and/e820026b-c5d5-475f-8c4f-b6730082e53e and https://stackoverflow.com/questions/16167478/executecomplete-adodb-connection-event-not-fired-with-adasyncexecute-parameter – June7 Mar 05 '19 at 23:42
  • 3
    Possible duplicate of [Running multiple async queries with ADODB - callbacks not always firing](https://stackoverflow.com/questions/21933099/running-multiple-async-queries-with-adodb-callbacks-not-always-firing) – June7 Mar 05 '19 at 23:48
  • June7 - Thanks once more. Works perfectly. That pointer was exactly what I needed. I UPDATED the code snippet above to reflect my modification. I did not see the issue noted by that OP, but will keep this link handy. – Mark Pelletier Mar 06 '19 at 01:20

0 Answers0