Batch processing

[Haji]

Hi,

I have an application that requires that some time
consuming queries be run. It would be ideal if I could
get these to run at night. I looked at the schedule
portion of the control panel which allows a program such
as MS Access or Excel to be opened at a certain time.
What I would like to do is to have a specific set of
queries in a specific MS Access Database run at a
specific time. Can anyone help with how to schedule jobs?

Thanks,

Haji

 

[Wayne Morgan]

You can use the command line to specify a macro to run, the macro could then run the
queries.

/x macro
Starts Access and runs the specified macro.

In the macro you would use either the RunSQL or RunCode command. I would recommend the
RunCode command and run the queries from there. This way you could place them all in a
Transaction and roll the changes back if an error occurs.

 

[Haji]

Wayne,

Thanks for your help. I am a little unfamiliar with this
process. I have created macros before like the autoexec
macro. Do I create a macro called /x? Also, how does
this job get schedule to run? Lets say I want my pc or
server to invoke this application and run this query at
3:00 am. One other thing is that when the query is
actually an append query which pops up a message
saying "You are about to paste 34567 records into your
table - is that OK?". Do I need to tell my macro to not
show this message since it may hang the processing of the
query until "Yes" is checked?

Thanks,

Haji

 

[Wayne Morgan]

The /x is a command line switch. You would use it in the command line of the Windows
Scheduler.

Example:
"<path>\msaccess.exe" "<path>\MyDatabase.mdb" /x NameOfMacro
Include any other command line switches you may usually use for user name, password,
workgroup file, etc.

You would have the macro call a VBA function that will run the queries that you want run.
The coding for that (which won't cause the prompt you mention) would be something similar
to below. As I mentioned before, you may want to wrap this in a transaction, so that if
any of it fails then it will all roll back.

Public Function RunOnSchedule()
On Error GoTo CheckError
Dim db As DAO.Database, strMsg As String
Dim wks As DAO.Workspace, bolInTransaction As Boolean
Set wks = DBEngine.Workspaces(0)
Set db = CurrentDb
wks.BeginTrans
bolInTransaction = True
db.Execute "qryMyFirstQuery", dbFailOnError
db.Execute "qryMySecondQuery", dbFailOnError
'Repeat the above command for each query.
'The dbFailOnError will send us to the error handler
'if the query should fail to complete properly.
'You could also use a SQL statement string instead of the
'name of the stored query.
wks.CommitTrans
bolInTransaction = False

:CleanUp
Set db = Nothing
Set wks = Nothing
Exit Function

:CheckError
If bolInTransaction = True Then
wks.RollBack
MsgBox "The queries failed!", vbOkOnly + vbCritical
'This MsgBox will stay there until you come in to click Ok.
'Make sure you don't set the Window Scheduler to force the
'database to close after a certain time.
Resume CleanUp 'You could remove this line to get
'a more detailed error message below.
End If
strMsg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox strMsg, vbOKOnly + vbExclamation, "Error", Err.HelpFile, Err.HelpContext
Resume CleanUp
End Function