How do I run VBA code in the background?

Posted on 2009-12-17
Medium Priority
Last Modified: 2013-11-28
Dear experts,
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 problem:
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.
Question by:drjdbb
LVL 77

Assisted Solution

peter57r earned 248 total points
ID: 26078338
To answer the Q in your title - you can't.  There is no 'background'.

However, your description of what you have done sounds OK to me.  You need to be sure that you have DoEvents at suitable points in the code to allow the button click to be acted upon and you are testing the public flag after each step.  Obviously the step running at the time you press the button  is going to complete before the button press is detected and the 'Stop' is actioned.
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 504 total points
ID: 26079578
Also, in regard to "leaving the application in various unstable states":

This is the real issue with this. I would NOT allow users to cancel the creation of the new database, or else I would ALWAYS delete the new database if the user clicks Cancel. Simply put, there is no way for you (through code) to determine whether that creation suceeds or fails, if the user clicks the Cancel button.

I'd also encourage you to NOT use an ActiveX control for this, since it's very simple to create a Progress bar using native Access controls. Here's a sample I have:

LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 248 total points
ID: 26080022
He's another.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 26110083
Thank you to all so far.
After Peter's response, I came across the Progress bar at this site:
which doesn't use activeX. Even though it claims to have the Cancel button click-able during processing of other code, it isn't. Maybe I'm doing something wrong. I cam accross another gliche - if you chose the Cancel button NOT visible argument, the form won't load, because the cancel button is the only control present that can receive the focus!. I created a work around for that, but I'm afraid I'm at a loss as to how DoEvents can help me.
I have howver come up with a solution that works - but only for an mdb file - which my production progrom won't be, so I'm still looking for a good solution. I shall look at LSM's and Jim's zip files to see if they can help.

Author Comment

ID: 26110231
So I've had a look at the two supplied Progressbars. Unfortunately, neither have a Cancel button.
The one from msdn has a cancel button, and I have already integrated it into my code.
When you update the meter (acbUpdateMeter()), it checks to see whether the cancel button has been pressed and returns True or False depending. This way, I can determine the route of action to take when the Cancel button is pressed. I hvae altered the original form as posted on the msdn website, to include:
1. When the Cancel button is pressed, the progress label changes to "Cancelling..."
2. I have added another label to the form, which meant adding another argument to the acbUpdateMeter function.
The way I have got it to work making the Cancel button click-able 100% of the time, is by opening a new instance of Access and exporting the Progress meter form (and associated modules) to a temp file. I then call the functions from that instance of access. As stated above, this works perfectly, except that when in an mde file, you can't export forms or code. I could simply have an extra mde file on the server waiting to be downloaded and accessed for this purpose - after all, this subroutine won't be clicked that often! - which will save me the hasle of trying to recrete the form exactly as in original via code.
However, having said that, I'm still interested in the DoEvents solution. Peter57r suggested that the DoEvents can do the job I ask, but I need to make sure I place it in the correct location. I have supplied some sample code from my routine (calling the acbUpdateMeter function as if it were in the current file). If anyone is able to suggest where to put the DoEvents in my code to make it work, I would be most grateful.
[ Explanation of code: This is part of the whole. The first time I call the acbUpdateMeter fn in this code, the second argument updates a label on the meter saying what step of the process we're up to. I use 1 as the starting percentage done, because if I feed 0 to the update function, the label shows "%" only and not "0%".  The second time the function is called, I supply only the percentage done.]
' Transfer all reports
With CurrentProject.AllReports
    intCounter = intCounter + 1
    If Not acbUpdateMeter(1, conStep(intCounter)) Then GoTo Cancelled
    iTot = .Count
    For j = 0 To iTot - 1
        Set obj = .Item(j)
        DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acReport, obj.Name, obj.Name
        If Not acbUpdateMeter(((j + 1) / iTot * 100)) Then GoTo Cancelled
    Next j
End With

Open in new window

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 504 total points
ID: 26111805
Most likely this would go in the acbUpdateMeter function, but as you've not supplied that it's have to say. Basically, DoEvents would go in whatever routine is used to increment the progress bar.

Note you can use DoEvents in multiple places ... for example, in the code above, you could place a DoEvents immediately after the For j=0 line ... this may or may not work to catch your user Cancel event click, however. You should do that in whatever code routine "looks" for that Cancel button push.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question