I have a Access 2000 database that is being updated thoughout the day. I need to make a snapshot of the data at 8:15 AM everyday and use this data as the record source for a report. We do this so all shops are working from the same report for a 24 hour period. To make the snapshot I have placed a command button on my main menu that runs a make-table query when pressed. We have a dedicated button pusher assigned to push the button everyday around 8:15. This works but it has its downsides.
I want have the database create the snapshot automatically. The problem that I encounter is two-fold. First, in order to run MY make-table query, I have to make sure the no one is editing the main table until after the new table is made. This means that if I tell Access to do the snapshot at 8:15 and someone is editing the main table, the query will not run. Second, the database is not running continuously throughout the day. If no one has the database open at 8:15, the code will not execute. Assuming I use if time = 8:15 then....
I'm think that if I write the VB in the On_Open callback to do the make-table at 8:15 or later and set flag to signify that the query has ran for that day I might be close. The problem would be when to clear the flag. It seems like there is going to be a lot of book keeping involved.
You could create a seperate database that runs from Windows Scheduler. At any time you could import the tables(overwrite it or archive it), generate the report, then shut down automatically. This could run regardless of whether your 'main' db is open or not.
Maybe this isn't feasible for you, but thought I'd throw it out there.
I tend to agree with cgpospi. If you put the logic in a separate database, and link to the table you want to create, then you can keep that database up and running without worrying about the other one. You might also consider dumping that table to a file instead. You can use the TransferText command to dump it to a text file and then use the files existence as your flag. After you created the file (using today's date in the filename) you can then link to it, give the linked table a generic name (so that your queries still work) and run your report. That way you will have a copy of the report in 6 months when someone asks for one they lost.
It's installed with Windows under System Tools. Very straightforward and handy for these types of things.
If I remember correctly, you'll need to create a simple batch file that starts your DB. Scheduler won't run a file that's not executable.
So something like
c:\program files\microsoft office\office11\msaccess.exe pathtoyourdb\yourdb.mdb
in a .BAT file will work.
Then I run all the code I need from a startup form with a Docmd.Quit at the end.