hellblazeruk
asked on
Using task scheduler to run macro or vba every day?
Hi,
I have a vba module within an Access database, I would like to schedule this vba script to run automatically every day unattended, can I call the script from windows task scheduler or should I use a batch file like below to run a macro and that macro run the vba script?
If a macro, what would that be?
@echo off
"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.
I have a vba module within an Access database, I would like to schedule this vba script to run automatically every day unattended, can I call the script from windows task scheduler or should I use a batch file like below to run a macro and that macro run the vba script?
If a macro, what would that be?
@echo off
"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.
NVIT
Whatever you choose, use task scheduler as the trigger and then run the command.
I've found it easier to do a batch command file and then call that because you can then run it outside of the scheduler.
Jim.
Jim.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may definitely want to check out @mx's suggestion.
Office programs are meant to run in the context of a user.
Trying to schedule a VBA routine to run when a user is NOT logged on is the beginning of grief.
It may work...or not...but is an unsupported scenario form MS's viewpoint, so if you have problems, you'll get no joy.
On the flipside, depending on what the code does, you may be able to transform it into VBScript and run it unattended.
Office programs are meant to run in the context of a user.
Trying to schedule a VBA routine to run when a user is NOT logged on is the beginning of grief.
It may work...or not...but is an unsupported scenario form MS's viewpoint, so if you have problems, you'll get no joy.
On the flipside, depending on what the code does, you may be able to transform it into VBScript and run it unattended.
I've had lots of difficulty getting task scheduler to kick off an Access application when my computer is running unattended. When it has worked, it works best using the method Jim mentioned above:
1. create a .BAT file with the commands for opening the correct version of Access and the appropriate Access .mdb or .accdb file
2. then run that .BAT file from the task scheduler.
I have not used the FMS Scheduler application, but have heard nothing but good comments about it. I personally have several Access applications which run continuously on several clients computers and use tables to identify when to kick off specific other Access applications or processes. I'm guessing that the FMS scheduler is similar to those, with a lot more bells and whistles.
1. create a .BAT file with the commands for opening the correct version of Access and the appropriate Access .mdb or .accdb file
2. then run that .BAT file from the task scheduler.
I have not used the FMS Scheduler application, but have heard nothing but good comments about it. I personally have several Access applications which run continuously on several clients computers and use tables to identify when to kick off specific other Access applications or processes. I'm guessing that the FMS scheduler is similar to those, with a lot more bells and whistles.
I've actually got a scheduler I wrote in Access. Didn't like the new one in Windows 2000 and up, so I duplicated the one in Windows XP.
Got it to run in the system tray and everything, but still, your limited to a foreground process (someone must be logged in).
I looked at a bunch of different schedulers before writing this one, and all struggled with running jobs in the background and of course as Nick and Dale pointed out, Office programs are pretty much designed to run as a foreground app. You can keep a computer locked, but a user must be logged in.
Jim.
Got it to run in the system tray and everything, but still, your limited to a foreground process (someone must be logged in).
I looked at a bunch of different schedulers before writing this one, and all struggled with running jobs in the background and of course as Nick and Dale pointed out, Office programs are pretty much designed to run as a foreground app. You can keep a computer locked, but a user must be logged in.
Jim.
ASKER
I plan to keep the user logged in and the computer locked, so just need a way of the vb script running at a certain time.
Task scheduler will do you fine then...
Jim.
Jim.
ASKER
when I run the .bat
@echo off
"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.
I get the popup:
Microsoft Access cannot find the object 'DailyUpload.'
the DailyUpload is not a macro its an object/module, do I need a macro called 'open' and that macro run the vba?
@echo off
"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.
I get the popup:
Microsoft Access cannot find the object 'DailyUpload.'
the DailyUpload is not a macro its an object/module, do I need a macro called 'open' and that macro run the vba?
You need either:
1. An autoexec macro that calls that procedure
2. A startup form that calls the procedure and is specified in startup.
3. If your going to use the /x switch, a macro named "DailyUpload" which calls the procedure.
I cover all the different ways of starting a DB here:
https://www.experts-exchange.com/Database/MS_Access/VP_537.html
Jim.
1. An autoexec macro that calls that procedure
2. A startup form that calls the procedure and is specified in startup.
3. If your going to use the /x switch, a macro named "DailyUpload" which calls the procedure.
I cover all the different ways of starting a DB here:
https://www.experts-exchange.com/Database/MS_Access/VP_537.html
Jim.
ASKER
Hi Jim,
Great video, I think the Autoexec macro is the option for me,
I am just looking at the macro option and cant find an option to open an object, I have a drop down list with many options where should I be looking?
Alex.
Great video, I think the Autoexec macro is the option for me,
I am just looking at the macro option and cant find an option to open an object, I have a drop down list with many options where should I be looking?
Alex.
You want "RunCode" in a macro to call a procedure, and "Open...." to open other objects (i.e. OpenForm)
Jim.
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Keep in mind that all sorts of other things besides Access can open database files.
Depending upon what "DailyUpload" does, it could be something that you could do entirely with VBScript.
If so, that eliminates 90% of the hassle as a VBScript can be scheduled to run regardless of login or not, and depending upon the situation of what is occurring, regardless of whether Access is installed or not.
If "DailyUpload" is opening one datasource and appending results to another, and little else, it's entirely scriptable.
Depending upon what "DailyUpload" does, it could be something that you could do entirely with VBScript.
If so, that eliminates 90% of the hassle as a VBScript can be scheduled to run regardless of login or not, and depending upon the situation of what is occurring, regardless of whether Access is installed or not.
If "DailyUpload" is opening one datasource and appending results to another, and little else, it's entirely scriptable.
<<If so, that eliminates 90% of the hassle as a VBScript can be scheduled to run regardless of login or not,>>
Excellent point.
Jim.
Excellent point.
Jim.
ASKER
Hi,
I may try the free trial of Total Visual Agent but thought it may be over kill for a small project that I am doing,
The script I am using to simple
Option Compare Database
Option Explicit
Public Sub Module1()
Const cPath As String = "C:\Export\"
Dim intFN As Integer
Dim strFilename As String
Dim colNames As New Collection
Dim vItem As Variant
Dim dtFileDate As Date
Dim qd As QueryDef
strFilename = Dir(cPath & "*.csv")
Do Until Len(strFilename) = 0
dtFileDate = DateSerial(Mid(strFilename
vItem = DLookup("ImportDate", "saleshistory", "ImportDate=#" & dtFileDate & "#")
If IsNull(vItem) Then
DBEngine(0)(0).Execute "Delete * From ImportData"
DoCmd.TransferText acImportDelim, "ImportSpec", "ImportData", cPath & strFilename, False
DBEngine(0)(0).Execute "UpdateChangedItemNames"
DBEngine(0)(0).Execute "AppendNewProducts"
DBEngine(0)(0).Execute "AppendSalesData"
Set qd = DBEngine(0)(0).QueryDefs("
qd!parmDate = dtFileDate
qd.Execute
End If
colNames.Add strFilename
strFilename = Dir()
Loop
For Each vItem In colNames
Name cPath & vItem As Replace(cPath & vItem, ".csv", ".txt")
Next
End Sub
I may try the free trial of Total Visual Agent but thought it may be over kill for a small project that I am doing,
The script I am using to simple
Option Compare Database
Option Explicit
Public Sub Module1()
Const cPath As String = "C:\Export\"
Dim intFN As Integer
Dim strFilename As String
Dim colNames As New Collection
Dim vItem As Variant
Dim dtFileDate As Date
Dim qd As QueryDef
strFilename = Dir(cPath & "*.csv")
Do Until Len(strFilename) = 0
dtFileDate = DateSerial(Mid(strFilename
vItem = DLookup("ImportDate", "saleshistory", "ImportDate=#" & dtFileDate & "#")
If IsNull(vItem) Then
DBEngine(0)(0).Execute "Delete * From ImportData"
DoCmd.TransferText acImportDelim, "ImportSpec", "ImportData", cPath & strFilename, False
DBEngine(0)(0).Execute "UpdateChangedItemNames"
DBEngine(0)(0).Execute "AppendNewProducts"
DBEngine(0)(0).Execute "AppendSalesData"
Set qd = DBEngine(0)(0).QueryDefs("
qd!parmDate = dtFileDate
qd.Execute
End If
colNames.Add strFilename
strFilename = Dir()
Loop
For Each vItem In colNames
Name cPath & vItem As Replace(cPath & vItem, ".csv", ".txt")
Next
End Sub