Solved

Using task scheduler to run macro or vba every day?

Posted on 2015-02-24
16
401 Views
Last Modified: 2015-03-02
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.EXE" "C:\POS\POSSales.accdb" /x DailyUpload
0
Comment
Question by:hellblazeruk
  • 6
  • 4
  • 2
  • +4
16 Comments
 
LVL 23

Expert Comment

by:NVIT
ID: 40629349
Whatever you choose, use task scheduler as the trigger and then run the command.
0
 
LVL 57
ID: 40629394
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.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 40629442
Here is exactly the tool you need, which I use daily (ie, have is scheduled) to do exactly what you are talking about:

http://www.fmsinc.com/MicrosoftAccess/Scheduler.html

It works flawlessly and has many cool features, easy to configure and so on. It can also run as a Windows Service if you like - so even on reboot - it will start up and run your scheduled events.

In my case, it runs a macro  (which call vba code) in two different databases in sequence overnight, except on weekends (programmable of course).

Check it out ...

mx
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40629561
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40629588
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
 
LVL 57
ID: 40630406
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.
0
 

Author Comment

by:hellblazeruk
ID: 40630475
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.
0
 
LVL 57
ID: 40630476
Task scheduler will do you fine then...

Jim.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:hellblazeruk
ID: 40630504
when I run the .bat
@echo off
"C:\Program Files\Microsoft Office 15\root\office15\MSACCESS.EXE" "C:\POS\POSSales.accdb" /x DailyUpload

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?
0
 
LVL 57
ID: 40630527
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:

http://www.experts-exchange.com/Database/MS_Access/VP_537.html

Jim.
0
 

Author Comment

by:hellblazeruk
ID: 40630669
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.
0
 
LVL 57
ID: 40630754
You want "RunCode" in a macro to call a procedure, and "Open...." to open other objects (i.e. OpenForm)

Jim.
0
 
LVL 10

Accepted Solution

by:
LukeChung-FMS earned 250 total points
ID: 40630805
MX: Thanks for the kind words and testimonial about your experience with Total Visual Agent.

Alex, I'm the owner and founder of FMS, the creators of Total Visual Agent.

Whether you use our product or not, system administration such as database compact and backups are important in the maintenance of Access solutions. Automating that and other repetitive chores can significantly simplify your obligations. Total Visual Agent is designed for enterprises where automatic restart, security, audit trails, email notification of any problems, etc. are important.

A free trial is available. Hope you like it. Let me know if you have any questions. Thanks.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40630928
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.
0
 
LVL 57
ID: 40630948
<<If so, that eliminates 90% of the hassle as a VBScript can be scheduled to run regardless of login or not,>>

Excellent point.

Jim.
0
 

Author Comment

by:hellblazeruk
ID: 40631389
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, 1, 4), Mid(strFilename, 5, 2), Mid(strFilename, 7, 2))
        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("UpdateNullImportDate")
            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
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now