frequently running an update query

[StephenB]

I have an update query that I need to run on a very frequent basis (including nights and weekends). I can run task scheduler, but I'm not sure if I can drill down to the update query to make that run off of the scheduler. I'm thinking of using a macro for this. Can anyone reccomend a sound way to do this? Also, does running the update query once every minute sound unreasonable?

Thank you for any insight.

 

[tberndt]

Stephen,

While you can run an update query once a minute, it's not the wisest thing to do. If you run the update query for more than a minute (it can happen - I've had some take that long and longer), then your plan to run once a minute become troublesome.

Also, because Access macros do not have error-handling built-in, any problems in running the update query will lead to a huge quagmire.

If you can live with it, I would suggest that you try something like half-hour or hourly updates. (Please realize that I do not know how volatile your data is and this thought may not meet your needs. But also realize that other tools exist that can better handle "live" data.)

The technique I've seen for using Access in a timed setting usually opens Access, opens the database, runs the AutoExec macro to run the query, then closes the database and Access. That takes up a lot of machine resources, and probably will not work for your situation.

You may need to use Access Modules (VBA or code) to get a viable solution.

HTH,

Tom

 

[StephenB]

Thanks, Tom. I'd like to stay away from macros all together. I understand MicroSoft wants to eventually do away with macros entirely. Everything I've done up until now has been in vba. However, I have no idea how to get this done using code.

You mention other tools to handle "live" data. Could you expand on that a bit?

Are you using Task Scheduler to open the db? (Not being familiar with macros) does an AutoExec macro start up on it's own? If you could comment more on your process, I'd appreciate it.
Thanks,
Stephen

 

[Twin]

Hi Stephen,
I have attached something you might be interested in...The database contains Macros as well as the same converted into VBA code. Note the setup of On Timer and Timer Interval on Form1 properties.
You can leave the database open at all times if it's going to help...
HTH

Chris

PS subform is not neccessary, just present for illustration purposes.

 

[tberndt]

Hi Stephen,

You're right, the AutoExec macro runs when the database is opened. From the AutoExec macro, I call the Code I want to run (RunCode).

I haven't used the Windows Task Manager to open the database. Rather, I've used a Scheduler Utility available through Dev Ashish's Access Web Site (click here).

While there are other tools to handle live data, none come to mind at this time. Without knowing how much data you need to process, it's a bit like asking how big or fast a car you need. It depends on how you'll use the vehicle.

Perhaps some other forum members can step in, too.

Tom

 

[StephenB]

Thank you both. The sample was very helpful. I've created the AutoExec macro (for learning purposes, eventually I'll convert over to vba) and it works fine whenever I manually open the db. However, even though task scheduler is stating that it opened and closed the db, and reports no problem, my data still reflects the last time the db was opened manually as the last update time. I've doublechecked all properties on the task scheduler event and everything seems fine as far as I can tell. Any suggestions on what I can check?

 

[tberndt]

Hi Stephen,

While you can convert your AutoExec macro to code, it won't work the same (the code won't work like the macro).

Microsoft designed Access to have the AutoExec macro "do its thing" when the database is opening. There was no provision for an AutoExec module to affect the startup. (Though you could have the AutoExec macro call the AutoExec module, but that can be confusing to the user, and who knows when the use of the same name will cause Access to hiccup. Not recommended.)

As for having the same dates/times after running your process, it might depend on how things are run. If you run an append query to add records to the table in question, and you have a default value of Now() for the UpdatedTime field, then you should see the date/time for when you appended the records.

HTH,

Tom

 

[StephenB]

Thanks for the heads up on the code. As for updating; I have an autoexec macro running the update query. It works fine (which is to say that my update field=now() reflects when I open the db), but it's as though task scheduler is not opening the db, even though it's not reporting any problems. When I open the db bypassing the macro (shift), the update field still reflects the last time the db was opened manually.