How do I run VBA code in the background?

Posted on 2009-12-17
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

    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 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    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 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    He's another.

    Author Comment

    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

    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 84

    Accepted Solution

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now