Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Scheduled Automation

    I have a report that management wants automatically e-mailed to them two times per day.

    I configured a database to open to a form that runs code on open to email the report. The vba works perfectly.

    I created a task in Task Scheduler, selected to run even when I'm not logged on. Everything works when I am logged on, but when I'm not logged on, it opens my database but does not run the vba. I log in and see that the task completed, I have the "lock file" indicating that my database is open, but when I look in task manager it's using 0% CPU, just not doing anything.

    Here are the methods I've tried:
    1. .bat file
    Code:
    cmd.exe /c "PATH TO MY DATABASE FILE"
    2. .vbs file
    Code:
    Dim sAcc
    Dim sFrontEnd
    Dim sSec
    Dim sUser
    Dim objShellDb
    Dim sComTxt
     
    sAcc = "C:\Program Files\Microsoft Office\OFFICE15\msaccess.exe"
    sFrontEnd = "PATH TO MY DATABASE FILE"
    
    Set objShellDb = CreateObject("WScript.Shell")
    'Build the command to launch the database
    sComTxt = chr(34) & sAcc & chr(34) &_
    	" " & chr(34) & sFrontEnd & chr(34) 
    if isNull(sSec)=False AND sSec<>"" Then
    	sComTxt = sComTxt & " /wrkgrp " & chr(34) & sSec & chr(34)
    End if
    if isNull(sUser)=False AND sUser<>"" Then
    	sComTxt = sComTxt & " /user " & sUser
    End if
    objShellDb.Run sComTxt 'Launch the database
    3. I have tried running msaccess.exe from task scheduler with the path to my database in arguments.


    All methods return the same result. How do I get my vba to run through Task Scheduler when no users are logged in?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not to sure why you'd need to use VBS for this...
    I'd just open up a copy of Access with and run a specified query or macro, using one of the command line options (switches) that identifies the query or macro to run
    Or have a cut down front end containing just the stuff needed for the batch processing and run that as an autoexec macro.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    This did not work either, got the same result when logged off.

    We have decided to just run a VM that is always on and auto logs on, this way the user is never not logged on and the scheduled task will run access as it should.

  4. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Chris nice move, I set up some timers at my last job on a VM to keep refreshing my SQL via VBA while using dynamic ranges to continually update the dashboard. I ran into weird locking issues as well and had to leave it open.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •