Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78

    Windows Task Scheduler .Bat File Macro

    I am trying to use Windows Task Scheduler to run a macro that is in an access database on my desktop.

    I have a .BAT file that has this in it:



    Code:
    "C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE" "C:\Users\mmickle\Desktop\Managed_Care_Database.accdb" /x TestRun
    In the Database I am opening I have a macro called TestRun that looks like this:

    Code:
    Sub TestRun()
    
        Dim xls     As Excel.Application
        Dim wkb     As Excel.Workbook
        Dim wks     As Excel.Worksheet
        
        Set xls = New Excel.Application
        Set wkb = xls.Workbooks.Add
        Set wks = wkb.Worksheets(1)
        
        wks.Name = "My New Name"
        ActiveWorkbook.SaveAs "C:\Users\mmickle\Desktop\WeAreInBusiness.xlsx"
        wkb.Close True
        
        Set wks = Nothing
        Set wkb = Nothing
        
        xls.Quit
        
        Set xls = Nothing
    End Sub
    When I set up the Task. It "runs" .... I see a flicker on my screen which I assume is Access Opening. I also look into the Task Scheduler and it says the task was run successfully. However, the Macro does not create a workbook on my desktop as I have specified. I assume the macro is not being triggered. I also tried putting the code in an autoexec macro. This didn't seem to work either.

    I have tested the macro independent of the Task Scheduler, so I know it works. I have also referenced the Excel 15.0 Object Library etc....

    Can someone please assist me in getting the macro to run successfully from Windows Task Scheduler??

    Thank you for the help.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    In Access, a Macro is an Object that you can see in the Navigation Pane. What you are describing there looks like a VBA code block. You could put your code in a form or in a Standard module.

    You can set it up so your code runs automatically at Start Up.

    Does your workbook get created when you double click and open your Access file?

    Also, why are you using a switch in your statement within your BAT? ( /x TestRun) I do not think this is a valid switch for an Access file. I could be wrong though.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's not a macro, it's VBA code. Excel confuses the issue by calling VBA code a macro; Access has both. You'd have to create a macro that called that code (which may need to be changed to a function).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by ItsMe View Post
    Also, why are you using a switch in your statement within your BAT? ( /x TestRun) I do not think this is a valid switch for an Access file. I could be wrong though.
    It is a valid command line switch to run a specific macro upon opening the db.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    Let me apologize for my unfamiliarity with Access terms/concepts in advance.

    ItsMe
    ,

    I looked around messageboards for a while and tested some .BAT files. I got this one to work:

    Code:
    "C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE" "C:\Users\mmickle\Desktop\Managed_Care_Database.accdb"
    In another messageboard post it referenced calling a macro like this: /x TestRun (so this could indeed be wrong)

    How should I go about this?

    pbaldy,

    I changed the code to this:

    It is in a regular code module named "MacroTest"

    Code:
    Function TestRun()
    
    
        Dim xls     As Excel.Application
        Dim wkb     As Excel.Workbook
        Dim wks     As Excel.Worksheet
        
        Set xls = New Excel.Application
        Set wkb = xls.Workbooks.Add
        Set wks = wkb.Worksheets(1)
        
        wks.Name = "My New Name"
        ActiveWorkbook.SaveAs "C:\Users\mmickle\Desktop\WeAreInBusiness.xlsx"
        wkb.Close True
        
        Set wks = Nothing
        Set wkb = Nothing
        
        xls.Quit
        
        Set xls = Nothing
    
    
    End Function
    Then I created a Macro using the Access UI named "Test1"

    Action: RunCode
    Function Name: TestRun

    When I tried to use this as a function it added an open parenthesis like this: "TestRun(" How/ Do I need to re-write the function?

    How would I call the Macro Test1 from Task Scheduler?

    Thanks for the assistance. I am fairly new to any MS Access Code/Macros. I only have experience with Excel VBA



    EDIT:

    I changed the Function Name: to
    TestRun()

    When I click the "Test1" Macro in Access it works. A new Workbook appears on my desktop. However when I try using the .BAT file

    Code:
    "C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE" "C:\Users\mmickle\Desktop\Managed_Care_Database.accdb" /x Test1
    No Dice. It opens MS Access but doesn't trigger the Macro.


  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Like Paul said, the switch is valid.

    In your Macro use TestRun()
    as the name of the function

    Then, name your edit: (MACRO) something else, like MyMacro

    Then your switch should work
    /x MyMacro

    Alternatively, you can name your Macro Autoexec and it will run every time your Access file opens (Regardless of a switch. Autoexec macro will run when the file is double clicked).
    Last edited by ItsMe; 05-12-2015 at 05:25 PM. Reason: replaced the word function

  7. #7
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    ItsMe,

    Thank you so much for the assistance. It appears that I am getting closer.

    I tried changing the Macro Name From Test1 to AutoExec and then simply use a .BAT file like this:

    Code:
    "C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE" "C:\Users\mmickle\Desktop\Managed_Care_Database.accdb"
    The AutoExec Macro works great when I doubleclick on the Database Icon on my Desktop (The WeAreInBusiness.xlsx file is created and stored on my desktop) However when I use the .BAT file it does not seem to work.

    As I mentioned before the MS Access File flickers on my screen very quickly when the .BAT file is executed.

    Is it possible that there is not enough time for the Macro to execute?

    Is there a way to extend the length the file is opened via the .BAT file? (Like another command) Can I open the database for say 5 seconds and then close it? (Long enough to run the macro?)

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If I recall correctly, I use VBA from within the database to close the database. In other words. If you do not see Access launch and open your DB, it probably is not open. The flicker is probably the batch file running.

    It's been a while since I have played around with batch files. Perhaps the syntax is not correct. You mentioned you were able to get one of your batch files to work, so I did not question it. Now I wonder...

    I use VB Script. Here is an example - adjust for your version of Office/Access
    https://www.accessforums.net/access/...tml#post223969

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've got a number of automated databases I run from Task Scheduler, but I could only get an mdb to get called, not an accdb. You might convert the db to that format and see if it works. As ItsMe mentioned, without being specifically quit the database should stay open, so I don't think it's actually running.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    pbaldy and ItsMe,

    You are correct. The file was not opening.

    I changed the file to a .vbs

    Code:
    Option Explicit
    Dim oShell
    'open the Access file
    Set oShell = CreateObject("WScript.Shell")
    oShell.Run """C:\Program Files\Microsoft Office\Office15\MSACCESS.EXE"" ""C:\Users\mmickle\Desktop\Managed_Care_Database.accdb"""
    Added this line to the Function:

    Code:
    Application.Quit
    Then Scheduled the Task to open the.VBS File

    PRESTO!!! Success!

    Thank you both for your patience but most of all your assistance!

    Itsme,

    Is there a specific way to run a macro using vb? For Example if I wanted to run a specific macro instead of the AutoExec? I obviously don't want to close the Application if another user accesses the file on a network...etc.

    pbaldy,

    I will look into changing the file extension.


    Thanks Again!





  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by mrmmickle1 View Post

    I will look into changing the file extension.
    No need since you have it working with the script.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by mrmmickle1 View Post
    ...For Example if I wanted to run a specific macro instead of the AutoExec? ...

    You could use the switch that you tried before. Paul mentioned it is a switch to launch a macro and I believe him. If you just want VBA to run a macro you can try, DoCmd.RunMacro "MacroName"

    Having said that, this may not be the best solution if you are trying to avoid a script closing a User's work. I use a PC that Users do not have access to when using the Scheduler. Access is a desktop DB. The machine that opens it has its own instance of the application running.

    Also, maybe you should split your DB files so you have a front end and back end. You could start splitting by breaking out the functionality of the macro into a stand alone file. Link the tables, add the function, add the macro, and launch that file to create the workbooks.

  13. #13
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    ItsMe,

    Thanks for the advice on splitting the database. I have been thinking about this alot lately. I have been running into issues getting users out certain database files in order to compact and repair them. I made a hidden form that activates when the user opens the database in order to trigger them to exit with a countdown...etc. However, it seems there is always someone in the database that is not at the office or who left it open before they left for lunch etc...

    If I split the database will that make it easier for me to Compact and Repair the database?

    What benefits does this provide? Does it make the database less likely to crash and or get corrupted? Does it allow more total users?

    My IT department keeps telling me that it is not possible to backup/archive an Access file if users are in the file? Is this true? If so is there a workaround to help back up the file?

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by mrmmickle1 View Post
    If I split the database will that make it easier for me to Compact and Repair the database?

    What benefits does this provide? Does it make the database less likely to crash and or get corrupted? Does it allow more total users?
    You should not have to compact and repair the back end if it never gets opened and is only linked to. However, you can use a switch to automate a compact and repair. You can set the options of your FE files to compact and repair on close or simply throw them away after a measured period of time. There are many benefits to a split DB like, easier to develop new applications, less likely to crash, it can handle more users, etc.

    Quote Originally Posted by mrmmickle1 View Post
    My IT department keeps telling me that it is not possible to backup/archive an Access file if users are in the file? Is this true? If so is there a workaround to help back up the file?
    It might be true. However, I have not had any issues running backups during off hours via the Windows Task Scheduler and a VBS file. For that matter, I have even right clicked and copied back end files while others were transferring data (not that I would recommend doing that). There are ways to tell if someone is connected to the backend but it seems like a lot of work to check. What if one person out of twenty decides to leave their application open on Wednesday evening. Then, Thursday evening another person leaves their app open. There are two days without a backup. Just copy it...

  15. #15
    mrmmickle1's Avatar
    mrmmickle1 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    North Carolina
    Posts
    78
    I have not had any issues running backups during off hours via the Windows Task Scheduler and a VBS file
    ItsMe,

    Do you happen to have any sample script for how to run these backups that I could look at as an example?

    Thanks Again for all of your assistance. It looks like I will definitely be splitting databases in the future.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. OnClick_Btn to Macro for Windows Scheduler
    By meg9222 in forum Macros
    Replies: 1
    Last Post: 08-09-2013, 01:42 PM
  2. Tricky Relationships in a Task Scheduler
    By igglebop in forum Database Design
    Replies: 2
    Last Post: 05-03-2012, 10:27 AM
  3. Windows 7 scheduler
    By faeren in forum Misc
    Replies: 1
    Last Post: 01-27-2012, 10:26 AM
  4. Macro having issues when run on Windows Scheduler
    By faeren in forum Programming
    Replies: 4
    Last Post: 09-15-2011, 09:59 AM
  5. Task Scheduler using Service Account
    By ExpertNovice in forum Access
    Replies: 2
    Last Post: 06-11-2010, 02:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums