ACC: Overnight Batch Considerations and Sample Code

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

As your use of Microsoft Access increases, you may find it necessary or convenient to run some of your Microsoft Access processes at night or at other times when you are absent.

This article describes how to run Microsoft Access processes at specified times without user intervention.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

Part 1 of this article includes sample code that starts two update queries. One of the queries is started at 11:00 P.M. and the other is started at 4:00 A.M. the next morning. In this example, you start the code by clicking a command button.

Part 2 contains a sample decision-support flow chart that details some of the processes and decisions you might use for unattended operations. You may want to use other graphic aids or decision-support software to represent your scenario.

Part 1: Steps to Produce Sample Job Stream

1. Create two new queries called Query1 and Query2. Base each of these queries on a different table (such as the Categories and Products tables) in the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0). Add one or more fields to each query grid.

2. In each query, click Make Table on the Query menu to convert the query to a make table query. Have Query1 make a table called Table1, and have Query2 make a table called Table2.

3. Run both queries. Verify in the Database window that the tables Table1 and Table2 have been created.

4. Create a blank new form with the following controls:

         Form: Batch Process Form
         ----------------------------------
         Text box:
            Name: Current Time
            ControlSource: =Now()
         Command button:
            Name: Initiate Batch Processes
            Caption: Run
   
   

5. Set the command button's OnClick property to the following event procedure.

   NOTE: In Microsoft Access version 1.x the OnClick property is called the OnPush property.

          Private Sub Initiate_Batch_Processes_Click()
   

            MsgBox (Now)
            MsgBox ("Use CTRL+BREAK to terminate manually.")
            On Error Resume Next
   
            '***************************************************************
            ' "On Error Resume Next" allows processing to continue if, for
            ' example, Table1 does not exist when the code tries to delete
            ' it. The code would then delete the other three tables and
            ' create four new tables. If you want to know when errors such
            ' as this occur, you must add more error-trapping code.
            '***************************************************************
   
            Do
               DoEvents
            Loop Until Now > CVDate(Date & " 11:00:00 PM")
   
            '***************************************************************
            ' Change the time to the time you want processing to begin. For
            ' example,
   
            '    Loop Until Now > "10/4/94 11:55:00 PM"
   
            '    -or-
   
            '    Loop Until Now > "10/5/94 12:05:00 AM"
   
            ' NOTE: Do not use leading zeros in dates. Use
   
            '    10/4/94 11:55:00 PM
   
            ' but not
   
            '    10/04/94 11:55:00 PM
   
            ' Leading zeros are optional in the time portion of the string.
            ' "AM" and "PM" are not case sensitive.
   
            ' Double-check the dates you enter, and make sure you have used
            ' "AM" and "PM" correctly. Each entry should have only two spaces
            ' (one between the date and the time, and the other between the
            ' time and "AM" or "PM"). If you get stuck in a loop, use
            ' CTRL+BREAK and then reset your code to start a new test. You
            ' can also use Control Panel's Date/Time icon to reset the
            ' computer's time.
   
      NOTE: the following code is divided into two sections: one for
      versions 7.0 and 97, and the other for versions 1.x and 2.0.
   
      In Microsoft Access 7.0 and 97, type:
   
            Application.SetOption "Confirm Action Queries", 0
            DoCmd.DeleteObject A_TABLE, "Table1"
            DoCmd.OpenQuery "Query1"
            Do
               DoEvents
            Loop Until Now > CVDate(Date & " 4:00:00 AM")
            DoCmd.DeleteObject A_TABLE, "Table2"
            DoCmd.OpenQuery "Query2"
            MsgBox ("Timed processes completed.")
            Application.SetOption "Confirm Action Queries", -1
         End Sub
   
      In Microsoft Access 1.x and 2.0 type:
   
            Application.SetOption "Confirm Action Queries", 0
            DoCmd DeleteObject A_TABLE, "Table1"
            DoCmd OpenQuery "Query1"
            Do
               DoEvents
            Loop Until Now > CVDate(Date & " 4:00:00 AM")
            DoCmd DeleteObject A_TABLE, "Table2"
            DoCmd OpenQuery "Query2"
            MsgBox ("Timed processes completed.")
            Application.SetOption "Confirm Action Queries", -1
         End Sub
   
   

Part 2: Sample Overnight Decision Considerations

The following considerations are for illustrative purposes only. You must carefully consider how and when to process your own data. You should test critical processes against test data before implementing unattended processing. Note that contingency procedures are especially important if follow-on processing must proceed early in the day.

You may want to use transaction processing to handle sets of processes that must succeed as a group or be rolled back to a starting point.

You may want to use Microsoft Project or other decision-support software to graphically represent your critical path to help you understand how to proceed in partial failure situations.

Sample Overnight Decision Flowchart:

   Do critical daily activity processes needed by tomorrow or ASAP
   (backups, accounts receivable, patient status, and so on).

   If critical processes fail? THEN
      Call or page primary responsible person or
         secondary responsible person or management.
      If some or all critical processes continue to fail
         or help is slow in arriving? THEN
            Run processes not dependent on previous failures.
      Else
         Continue to analyze the problem and wait for help.
   Else
         Do any remaining daily activity updates.
         Do nightly backups after updating. (It is your business
            decision whether to back up your data before or after nightly
            processing, or both before and after.)
         Do reporting.
         Do user-specific batch SQL requests.
         Upon returning, check status of processes.

REFERENCES

For more information about transaction processing, search the Help Index for "Transactions, processing."

For more information about using loops, search the Help Index for "Loops."