Schedule MS Access Module

问题:

is it possible inside MS Access to schedule module to run every day at particular time ? If it is not possible, what is the best and easiest way to schedule module inside MS Access to run every day ?

Script is using to export table from MS Access to xls file and looks like this

 Dim outputFileName As String
 outputFileName = CurrentProject.Path & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "bolnickiracun", outputFileName, True

回答1:

2 Solutions

---

Form timer

This solution require that you let the Access Application open at all the time.

Create a form

In the form events:

• Set the Timer interval to 60000

• Make a On Timer procedure where you call your module's sub

The form should be open to trigger the timer. You can make it open when the application starts in the options/current database/display form

---

Windows scheduled task

This solution is better in my opinion as it doesnt require the MS access application to be running.

In the Access application :

1. Create a new macro and name it "AutoExec" so it will be triggered when the application starts.

2. Open the macro in design view and add a new action of type RunCode, under Function Name add the main sub or function of your module.

3. At the end of your module's sub, add this to close the MS access application when the code has been executed : docmd.Quit

In Windows :

4. Create a batch file anywhere (new file named anything.bat)

5. Edit your batch file and add this code to it (adapt path / accdb name accordingly of course)

   start "" "C:\pathToTheApplication\MSAccessAppName.accdb"
   Exit
   

6. Create a task in the Windows Task Scheduler (start menu and search for Task Scheduler) that will trigger your batch file when you want. Google how to do this or just look here for some ideas

回答2:

Use the Windows Task Scheduler to open Access.

http://www.sevenforums.com/tutorials/11949-elevated-program-shortcut-without-uac-prompt-create.html

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.visible = true

accessApp.UserControl = true

accessApp.OpenCurrentDataBase("C:\path.accdb")
accessApp.Run "myLinker"

Also, once Access is open by the Task Scheduler, you can control other events.

Open

Open happens before Load and allows you to cancel so it doesn't open. It also allows access to OpenArgs. This can be helpful if your form requires user input. If it is not supplied, you can cancel the Form.Open or prompt the user for needed values.

Private Sub Form_Open(Cancel As Integer)
     If "" & OpenArgs = "" Then
         Cancel = True
         Msgbox "Open Arguments are required"
     End If 
End Sub

Load

Load happens after Open and lacks any of the control Open provides.

Private Sub Form_Load()
    Me.Caption = Date
End Sub