0

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
Thomas G
  • 9,886
  • 7
  • 28
  • 41
MPetrovic
  • 123
  • 1
  • 5
  • 18
  • use the forms timer and leave the DB open, if you are doing from within access. – Nathan_Sav Dec 01 '16 at 12:35
  • @Nathan_Sav problem is that, there is about 10 Access DB, and i want to export every db in .xlsx and after that to schedule automate import in MySQL, i have done that part, but i don't know is it possible to schedule model in Access, and DB are pretty large, and with constantly opened all Access files, RAM would be constantly be full – MPetrovic Dec 01 '16 at 12:40
  • Check out this: http://stackoverflow.com/questions/20245053/running-microsoft-access-as-a-scheduled-task – Preston Dec 01 '16 at 12:53
  • @tom preston i tried that solution, but for me it's not working, or i don't know to do like that, also i don't understand what means /x and what is needed to put instead "DoSomething in this line " C:\Users\Public\schedTest.accdb /x DoSomething – MPetrovic Dec 01 '16 at 13:09
  • Create 1 linked DB, that controls it and use the form timer to open relevant access db and do what it needs to. A table with db name, time to execute, macro to execute etc... – Nathan_Sav Dec 01 '16 at 13:11

2 Answers2

4

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 :

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

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

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

  3. 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

Community
  • 1
  • 1
Thomas G
  • 9,886
  • 7
  • 28
  • 41
  • i get error it say that i need to "Verify that each name in the expression refers to a unique object", what is problem ? – MPetrovic Dec 01 '16 at 13:29
  • @MPetrovic Glad to hear, but wich solution did you implement ? – Thomas G Dec 01 '16 at 13:53
  • 1
    i have used second solution, "Windows Scheduled Task". Also, i have one mora question. If we set to automaticly open Access , and after runing query to close Access, there is not any more posibility to normaln use that Access db , and then that's not good, but, if i linked DB in another Access and in that access to perform this steps, my question is, will than these steps affect original DB if i do this in linked DB? – MPetrovic Dec 01 '16 at 14:28
  • Well, first explain why "_and after runing query to close Access, there is not any more posibility to normaln use that Access db , and then that's not good_". there should'nt be any problem with this, and I don't really understand what the problem is. – Thomas G Dec 01 '16 at 14:45
  • you said that is good to add docmd.Quit at the end of script to automaticly after getting what i want to close Access, also u said that, i need to add AutoExec to be name of macro to automaticly run macro. Problem is that, if u try to normal open that Access DB, it will automaticly run macro AutoExec and after successful execution it will close Access. There is no possibility to open that particular Access and to do some stuff with tables, or to make report, or something else, if in the background AutoExec macro is waiting for launching that particualr Access, i hope i was more clear – MPetrovic Dec 01 '16 at 14:52
  • ok got it. There are multiple solutions to overcome this issue. The most simple is that you don't change anything and if you want to manually open your DB to work on it, you hold the SHIFT key when you open it, which will prevent the macro to be triggered. If this is not an acceptable solution, then you can just duplicate your database, or make an empty database from which you call the module of the other database,there are plenty of examples of this on Google :) – Thomas G Dec 01 '16 at 15:39
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/129552/discussion-between-mpetrovic-and-thomas-g). – MPetrovic Dec 01 '16 at 15:41
  • I can't chat because I am at the office. And almost on my way to home! – Thomas G Dec 01 '16 at 15:51
0

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
ASH
  • 20,759
  • 19
  • 87
  • 200