Microsoft KB Archive/125772

= ACC: Overnight Batch Considerations and Sample Code =

Article ID: 125772

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q125772





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 some other time when you are not there. This article shows you 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
 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. 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. Run both queries. Verify in the Database window that the tables Table1 and Table2 have been created.  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  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: 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.

<div class="references_section">