Making an Update Query Automatic

[Guest]

Hi,

I have a database with one regular table and a few ODBC linked tables.

I regularly need to update my regular table based on more current data in
the linked tables that are connected to my company's main database. This way
my table will always have the most current address and phone number
information.

Is there a way I can make an update query that automatically runs overnight,
at a certain time, like 4am?

Thanks in advance

 

[John Webb via AccessMonster.com]

BDM,

One method I can think of is to use the Windows Scheduler.

Set up your update query as normal, then build an AutoExec macro that runs
it. Set Windows Scheduler to open your database at a set time, and the
AutoExec macro should start the query for you.

I'm sure there are more elegant solutions out there, but I just finished
work and my head is sitting quite nicely in "relax" mode at the moment.

Cheers

John Webb

 

[Guest]

Not sure if this option requires the database to be open, or if it would do
the update automatically once it is scheduled, but similar to the response
below I ran across VBA code in an Excel help file on the command OnTime()..
it uses Application.OnTime() and can run an event every so often, or at a set
time. Check out the VBA help (opening a module for edit, and then going to
the help from there, and look up OnTime)

 

[Guest]

Ok,

Thanks guys, I'll check out both of those options.

 

[John Webb via AccessMonster.com]

The onTime event does need the application to be open.

One further problem would be that using the Windows Scheduler would need
your PC to be left on (I believe).

 

[AFSSkier]

I know this tread is several years old, but was never completely answered.
Here's for future readers:

Create an Access macro for your queries. Right click on the macro name,
create short cut for your desk top or drag the macro to your desk top. Open
windows scheduler, Add Scheduled Task, next, Browse to find the short to your
Access macro. Answer the task info (daily, weekly, etc.), scheduled time.
The login & password is for your network and/or windows. Open advanced
properties, finish, answer any additional info. It's as easy as that.

Your PC or server, where you schedule the task has to remain on & fully
booted. It will auto-run when you boot up your PC, even after turning it off
for the night.

I have several of these that run in the middle of the night. I saves time
waiting for an update or report to run.