scheduling update query to run automatically?

[norm]

Is it possible to have an update query run at a set time or interval? or to
have it run when ever the database is shut down?

 

[DanRoss]

You can youse VBScript to connect and execute the update then schedule it
via TaskManager or some scheduling tool.

 

[John W. Vinson]

Is it possible to have an update query run at a set time or interval? or to
have it run when ever the database is shut down?

The most straightforward way to do this is to create a Macro (named RunIt
perhaps) which uses a Runquery action to run the query, followed by a Quit
action to close the database. Then you can use Windows Scheduler to launch
msaccess.exe at the desired time and frequency; in the command line use

"%programfiles%\Office12\Office\msaccess.exe" "D:\path\yourdatabase.mdb" /excl
/x RunIt

using the actual path to the Access executable and to your database.

John W. Vinson [MVP]

 

[Carrie MT]

The most straightforward way to do this is to create a Macro (named RunIt
perhaps) which uses a Runquery action to run the query, followed by a Quit
action to close the database. Then you can use Windows Scheduler to launch
msaccess.exe at the desired time and frequency; in the command line use

"%programfiles%\Office12\Office\msaccess.exe" "D:\path\yourdatabase.mdb" /excl
/x RunIt

using the actual path to the Access executable and to your database.

John W. Vinson [MVP]

I have limited knowledge of access but I have managed to follow your
directions perfectly. I have just one problem. The query is accessing data
through a link to an SQL so to run the macro it is asking me for the ODBC
login and password. I can enter the login and password and it will finish
just fine but it's not really automated if I have to sit here and do that.
Any suggestions for that part?

Thank-you!

 

[Albert D. Kallal]

I have limited knowledge of access but I have managed to follow your
directions perfectly. I have just one problem. The query is accessing
data
through a link to an SQL so to run the macro it is asking me for the ODBC
login and password. I can enter the login and password and it will finish
just fine but it's not really automated if I have to sit here and do that.
Any suggestions for that part?

If you using a standard linked table to sql server, then simply delete the
linked table, and re-link that table to sql server. On the "last" step when
you choose the table, there is a check box to "remember" password.

so, if you check that box when linking the table you not get a logon
prompts....

And, for scheduling ms-access using windows scheduler, I have a nice
solution here you might consider:


http://www.members.shaw.ca/AlbertKallal//BatchJobs/Index.html

 

[Carrie MT]

Thank-you! So simple - it worked perfectly! I will take a look at the
scheduled task as a window script.