ACC2000: How to Use Code to Run Processes at Specific Times Without User Intervention

As your use of Microsoft Access increases, you may find it necessary or convenient to run some of your Access processes at night or at some other time when you are not present. This article shows you how to run Access processes at specified times without user intervention.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Part 1 of this article includes sample code that runs 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 following 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 that you may use for unattended operations. You may also want to use other graphic aids or decision-support software to represent your scenario.

Part 1: Steps to Produce Sample Job Stream

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new query based on the Categories table and add the field Category Name to the query grid.
3. On the Query menu click Make-Table Query to convert the query to a make-table query, and then type tblCategoryTemp in the Table Name box.
4. Save the query as qryCategoryTemp.
5. Create a second new query based on the Products table and add the field Product Name to the query grid. On the Query menu, click Make-Table Query, type tblProductsTemp in the Table Name box, and then save the query as qryProductsTemp.
6. Run both queries and verify in the Database window that the tables tblCategoryTemp and tblProductsTemp have been created.
7. Create a new form, not based on any table or query, with the following controls:
   Form: frmBatchProcess
   
   Text box
   Name: txtCurrentTime
   ControlSource: =Now()
   
   Command button
   Name: cmdStartBatch
   Caption: Run
8. Type or paste the following event procedure as the command button's On Click property:

   Private Sub cmdStartBatch_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, a table does not exist when the code tries to delete
       ' it. The code would then delete the other table and
       ' create two 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(#5/6/2000 11:00:00 PM#)
       
       '***************************************************************
       ' Change the time to the time you want processing to begin. Take
       ' care with date formats; Medium Date is most reliable.
       ' For example, typing
       '
       '    Loop Until Now > #6-May-00 11:00:00 PM#
       '
       ' will result in a reliable date interpretation by the VB Editor,
       ' while typing
       '
       '    Loop Until Now > #6/5/2000 11:00:00 PM#
       '
       ' can be ambiguous as the interpretation depends on machine settings.
       '
       ' 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 system clock.
       '*************************************************************    
   
       Application.SetOption "Confirm Action Queries", 0
       DoCmd.DeleteObject A_TABLE, "tblCategoriesTemp"
       DoCmd.OpenQuery "qryCategoriesTemp"
   
       Do
          DoEvents
       Loop Until Now > CVDate(#5/7/2000 4:00:00 AM#)
   
       DoCmd.DeleteObject A_TABLE, "tblProductsTemp"
       DoCmd.OpenQuery "qryProductsTemp"
       MsgBox ("Timed processes completed.")
       Application.SetOption "Confirm Action Queries", -1
       
   End Sub
   					

Part 2: 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.

• Consider using transaction processing to handle sets of processes that must succeed as a group or be rolled back to a starting point.
• Consider using 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

1. Perform critical daily activity processes needed by the next day or as soon as possible (backups, accounts receivable, patient status, and so on).
2. If critical processes fail:
   1. Call or page the primary responsible person or the secondary responsible person, or contact management.
   2. If some or all critical processes continue to fail or help is slow in arriving:
      • Run those processes that are not dependent on previous failures.
        
        -or-
      • Continue to analyze the problem and wait for help.
3. Otherwise:
   1. Perform any remaining daily activity updates.
   2. Perform 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.)
   3. Carry out reporting.
   4. Perform user-specific batch SQL requests.
   5. Upon returning, check the status of your processes.

For more information about transaction processing, click Microsoft Access Help on the Help menu, type usetransaction property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about using DoEvents and loops, click Microsoft Access Help on the Help menu, type doevents function example in the Office Assistant or the Answer Wizard, and then click Search to view the topic.