Run ACCESS module from .bat file

I am trying to schedule a distribution of a report to run in ACCESS every Monday morning.  I have a module in ACCESS that does the logic I need and it works.  However, when I try to schedule it to run using my Task Scheduler and a .bat file, I can't get the module to run successfully.  It is waiting for me to select the macro to run.

my .bat file:
START /WAIT Msaccess.exe "\\Accounting\Users\Gina Leslie shared folder\IDX Reporting DB.mdb" /x "MyMacro"

MyMacro:
    DoCmd.OpenModule "Distribute Reports", ""
    DoCmd.Close acModule, "Distribute Reports"


Distribute Reports (module):
Option Compare Database

Dim sFile As String
Dim smgrFolder As String
Dim smgrName, smgrID As String

Dim iRecNum As Integer


Public Sub CycleThruMgrs()
    DoCmd.OpenForm "frmMgrs", acNormal, "", "", acEdit, acIcon
    iRecNum = 0
   
    Do Until iRecNum = 1
        iRecNum = iRecNum + 1
        smgrName = Forms!frmMgrs!LName.Value
        smgrID = Forms!frmMgrs!EmployeeID.Value
        smgrFolder = Forms!frmMgrs!REPORT_FOLDER.Value
        OutputReports
        DoCmd.GoToRecord acForm, "frmMgrs", acNext, 1
    Loop
   
    DoCmd.Close acForm, "frmMgrs"
End Sub



Public Sub OutputReports()
'
'######### Future Appointments Excel Report ##############
'
' Assign file name for the report file
    sFile = smgrFolder & "\Patients with Future appts.xls"
' Open the query
    DoCmd.OpenQuery "Patients with Future appts", acViewNormal
' Write the query out to Mgr's folder
    DoCmd.OutputTo acOutputQuery, "Patients with Future appts", acFormatXLS, sFile, False
' Close the query
    DoCmd.Close acQuery, "Patients with Future appts"
'
'
'######### Patients with no appt Excel Report ##############
'
' Assign file name for the report file
    sFile = smgrFolder & "\Patients with no appt.xls"
' Open the query
    DoCmd.OpenQuery "Patients with no appt", acViewNormal
' Write the query out to Mgr's folder
    DoCmd.OutputTo acOutputQuery, "Patients with no appt", acFormatXLS, sFile, False
' Close the query
    DoCmd.Close acQuery, "Patients with no appt"
'
'
End Sub


Thanks in advance for any help.

Start Free Trial