0

within my Excel sub I am calling an Access sub procedure that runs in the background (DB does not open) and updates a table in my DB. Everything works perfect, except that my Excel Sub will not proceed to the next line of code until Access is done running it's sub I called from Excel. So, my question is this...is there any way to call/run an Access Macro/Sub procedure from within an Excel Sub and have Excel proceed through the rest of the code and not have to wait for the Access Macro/sub to finish in order to proceed? Code: below:

    Set acObj = CreateObject("Access.Application")
    acObj.Application.Visible = False
    acObj.OpenCurrentDatabase "C:\Intraday Data\Intraday.accdb"
    acObj.Application.Run "RunData"

    MsgBox "Done!"

So Basically I want to get the done prompt right away w/o having to wait 30 seconds for the access procedure to finish...anyone have any insight on this they can share w/ me please? Thanks!

Erik A
  • 31,639
  • 12
  • 42
  • 67
wlfente
  • 133
  • 1
  • 10
  • It may depend on what the Access routine does. If you can replicate it with ADO, you can run that asynchronously. – Rory Jul 20 '14 at 07:45
  • The Access routine is just running a make table query to update a table w/ updated data... – wlfente Jul 20 '14 at 08:02
  • I believe asynchronous connections in ADO only functions as intended against a server (eg within a separate process). the fundamental issue is process threading, all calls within a process (application) are queued; a single thread cannot execute 2 operations simultaneously. the alternative is a scripting approach: http://stackoverflow.com/questions/4814294/running-asynchronous-query-in-ms-access – Tim Jul 21 '14 at 03:10
  • No - you can do it with Access too. See my sample code below. – Rory Jul 21 '14 at 07:48
  • You're quite right Rory, it's been a while since I worked with ADO! thanks for the refresher, and apologies for the incorrect statement :) I believe the point about processes still stands, but I'm thinking ADO creates a separate process in order to make this possible – Tim Jul 21 '14 at 08:16

2 Answers2

1

Here's some simple code to demonstrate an asynchronous call to an Access procedure:

Sub ExecuteAccessActionQuery()
' Sample demonstrating how to execute an action query in an Access accdb asynchronously
' Requires a reference to a Microsoft ActiveX Data Objects library
    Dim cn                    As ADODB.Connection
    Dim strQuery              As String
    Dim strPathToDB           As String
    Dim dTimer As Double

    ' Change path and query name as necessary
    strPathToDB = "C:\some path\database name.accdb"
    strQuery = "qmtTempTable"

    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & strPathToDB & ";"
        .Open
    End With
    dTimer = Timer
    cn.Execute strQuery, , adCmdStoredProc + adAsyncExecute
    MsgBox Timer - dTimer
    Set cn = Nothing
End Sub
Rory
  • 32,730
  • 5
  • 32
  • 35
0

You can emulate an asynchronous call in VBA by creating a trigger in the client application which will then execute the required subroutine. This technique is described well here (note the 1 second time delay required a few posts down):

http://social.msdn.microsoft.com/Forums/en-US/0546f8eb-d786-4037-906e-1ee5d42e7484/asynchronous-applicationrun-call?forum=isvvba

Currently, you are manipulating data in the Access tables without initiating the application, so you can either use a new instance of Excel in which to create the trigger, or you can open the Access application and do it there (http://msdn.microsoft.com/en-us/library/office/aa213969%28v=office.11%29.aspx). Either way, you must have another independent application (process) within which to execute your 'asynchronous' routine.

Tim
  • 756
  • 1
  • 7
  • 12