MS Access VBA: fire a function located on db2 from db1 and return back to db1 without waiting the code completion on db2

Question

I spent the whole day looking for an answer to this. I am working on an existing MS Access solution for a client, split into FE/BE. the data in the BE need to be synched with Azure SQL. I developed the class that handles this part with no issues. the problem is that when the Sync Job is fired, it takes a lot of time, and access being a single thread system, the users have to wait, and the customer doesn't like it.

So, what I did is that I moved the Sync Class to the BE part, so it operates independently from the FE, especially that the data is imported first in stage tables then consumed afterwards, so no interference with the users on the FE.

The problem is, when I tried to fire the Sync function from the class on the BE using Access.Application and OpenCurrentDatabase, the same thing happens; I have to wait till the process on the BE finishes so the FE gets the focus back.

I created an Autoexec macro that runs the "Sync" job when the BE opens, and then tried to open the BE using "Shell" from the FE (two independent instances of access), but the BE is password protected (Access 2016) and can't be open via shell.

all I want now is a way to fire the Sync job on the BE and return to the FE immediately. I can't find a way. can anyone help me with this ?

Thanks in advance !

score 0 · Answer 1 · answered Apr 06 '18 at 20:16

0

You need to let the first instance launch a second instance of Access which runs your sync/helper application - even if you closed down the first instance.

Shell can be used for that.

Don't use the backend for this. Create a dedicated helper application.

answered Apr 06 '18 at 20:16

Gustav

53,498
7
29
55

Thanks Gustav, that's exactly what I am not able to accomplish. Both instances are MS Access 2016 and the second instance is password protected. can you share the code that helps me do this with a password protected 2016 accdb? – Omar Kamel Apr 07 '18 at 09:20
That is not possible from the command line. But you might be able to open a dummy database that opens the real helper database: `Set db = DBEngine(0).OpenDatabase("d:\path\dummy.accdb", False, False, ";pwd=ThePassword1")` and then use this object, though I'm not sure how. – Gustav Apr 07 '18 at 11:46

score 0 · Accepted Answer · answered Apr 07 '18 at 16:16

You can use the technique I shared here to asynchronously run code in Access: use a form timer to make commands execute asynchronously. It requires a form to be present in the database executing the code.

In the database opening the second database:

Dim A As Access.Application 'Outside of sub
Public Sub RunTaskAsync()
    Set A = New Access.Application
    A.OpenCurrentDatabase "C:\Path\To\Database.accdb", , "password"
    A.DoCmd.OpenForm "ExecuteTask"
    A.Forms("ExecuteTask").TimerInterval = 1
End Sub

In a form called ExecuteTask in the database getting opened:

Private Sub Form_Timer()
    DoCmd.RunMacro "TheMacro"
    Application.Quit
End Sub

You need to remove any AutoExec macro's, those are getting executed synchronously.

MS Access VBA: fire a function located on db2 from db1 and return back to db1 without waiting the code completion on db2

2 Answers2