ID: Q125772
The information in this article applies to:
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
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.
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.
query to a make table query. Have Query1 make a table called Table1,
and have Query2 make a table called Table2.
Table1 and Table2 have been created.
Form: Batch Process Form
----------------------------------
Text box:
Name: Current Time
ControlSource: =Now()
Command button:
Name: Initiate Batch Processes
Caption: Run
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
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.
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."
Additional query words: archive
Keywords : kbprg MdlOthr PgmPrcs
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbinfoLast Reviewed: November 21, 1998