How do I run VBA code in the background?
Posted on 2009-12-17
I'm using Access 2003. My frontend and backends are secured mde files.
I have a subroutine that activates when I press a command button on Form1 that creates a new database with all the data from the backend files into an unsecured mdb file. (see Thread #24912990 )
The process of creating the new db takes some time. So, I would like to show a progress meter for the user, and allow for the user to cancel the process. I have played with the syscmd(acCmdInitMeter), but this is too subtle for my liking, and doesn't allow the user to cancel.
SO I have created a form (frmProgMeter) that containsa command button (Caption:Cancel) and 2x ActiveX progress meter bars and appropriate labels that are updated as the process runs.
However, as the process runs, I can't click on the cancel button. What I would like to do is have the subroutine run in the background, so that the cancel button can be clicked by the user. My plan was to have a global variable (bActive) set to False when the user clicked Cancel, and then have the subroutine periodically check (afte each step) if bActive is False, and if so, return the various databases back to original state, before closing the frmProgMeter.
I tried DoEvents in a couple of spots (in the subroutine, and in the frmProgMeter_OnOpen event) to no avail.
Not sure what the best way to do this is.
Should I have the CreateDB subroutine part of the button on Form1, or nested in the frmProgMeter code somehwere?
How do I allow the user to cancel the process and capture this easily in code, given that a simple break in code could occur durin any of a number of processes, leaving the application in various unstable states?
Any advice would be great.