Macro on a timer (Access 2000)

Topic

New Reply

Is there a relatively easy way to shedule a macro to run at a certain time each day?

Reply | Quote

Replies

You could create a scheduled task in Windows (through the Scheduled Tasks control panel) that executes a command line like this:

“C:Program FilesMicrosoft OfficeOfficeMSAccess.exe” “C:AccessMyDatabase.mdb” /x “MyMacro”

You can set the time and frequency for the scheduled task. The macro could quit Access after performing its actions. This method does not require that the database remain open all the time.

Alternatively, you could create a form with an On Timer event that periodically checks the time, and executes the macro at the appropriate time. You’d have to build in a check to prevent the macro from being run more than once. This method requires that the database remains open all the time, and that the form remains open in the database.

Reply | Quote

Hans –
You’re helping me with this question about Access and I have another email in my box from you for Outlook.
thank you – thank you

I did well with the first part of this but after I got to MSAcess.exe, it moved on and I don’t see where I can type in the second part .
I’m afraid to hit FINISH which make me think that I should probably find out how I cancel a job in the event that I botch up or it’s no longer needed.

I used explorer to open up MSAcess.exe to see what that was and it looks like the normal screen that I get when I want to open or create a new database. Do I select it there? Hey-Hey I may be on to something!

If I schedule multiple jobs are they listed somewhere so I can track everything that I have scheduled?

Reply | Quote

You can see already scheduled tasks in the same control panel, and you can edit and delete them there.

You’re on the right track. Tick the check box to see advanced options before clicking Finish. Or click Finish, then right-click the new task in the Scheduled Tasks window and click Properties. You can then change the target of the task to the line I posted.

Reply | Quote

Great – thanks.

Reply | Quote

Hans,
Is that whole command going on the Run line or is it suppose to be broken up between Run and Start with? I’m probably messing up the slashes, quotes, spaces and I’m not really sure what the ‘x’ is but I’ve tried every combination that I can think of. I’m sorry to ask for more assistance but the status for everything is Could not start.

Test database saved as:
C drive
Access folder
database: MyDatabase
macro names: MyMacro

“C:Program FilesMicrosoft OfficeOfficeMSAccess.exe” “C:AccessMyDatabase.mdb” /x “MyMacro”

Reply | Quote

It should all be in the Run box. The Start In box is not important, you can leave it empty.

I assume that you have substituted the correct path and filename of the database, and the correct name of the macro. The line I posted was just an example.

The /x tells Access to run the macro named after /x when the database is opened.

Have you ticked “Run only if logged in” in the Task tab of the properties of the scheduled task?

Reply | Quote

I had given my original test database a different name and placed it in a different drive and folder but when it wouldn’t run after numerous tries, I decided to set everything up exactly as you had it in your example so I could copy and past your command on the Run line.

I don’t have this option on my screen: “Run only if logged in”

Reply | Quote

What version of Windows are you running? The screenshot I posted is for Windows XP.

Reply | Quote

Windows 2000

Reply | Quote

I tested a scheduled task with the command line you posted; it worked without problems in Windows XP, so the command line itself should be OK.

Perhaps someone else will have a suggestion, otherwise you might ask in the Windows 2000 forum to get a wider audience.

Reply | Quote

Thank you for all of your support Hans.

Reply | Quote