Unattended Append Query

[Bruce]

I am running Access 2002. I have an Append Query that I want to run every
morning at 12.01 AM. Is is possible to have this query run every day at the
time specified without being logged on. If not possible being logged off,
then I can work out being logged on.

Any help in this area would be greatly appreciated.

 

[Clifford Bass]

Hi Bruce,

Pretty easy. Here is one way: Create a separate database with linked
tables to the data database. Create the append query and a macro that runs
the query and then exits out of Access. Name the macro AutoExec. Next
create a scheduled task (Start menu, All Programs, Accessories, System Tools,
Scheduled Tasks) that opens up your newly created database. Set the run time
as appropriate. If your data database is on the network you will need to set
it to run as a specific user with access to the appropriate network share.
Otherwise it may be able to run as SYSTEM. I would suggest testing with a
test copy of the database before implementing it for real.

Clifford Bass

 

[Bruce]

Clifford,

Thank you for your reply.

I did as you said and it works as designed, however I still have a few
questions before I implement:

Do I have to be logged on for Task Scheduler to work? Or does it run as a
service?

Is there any other way to perform this task? The reason I ask is because
everytime you open that database, it performs the task, unless I hold down
the shift key. If I were to forget, it would run the append query again and
duplicate data for same day.

Are there any tools in Access 2007 that are similar to SQL Server where you
can run specific programs/queries?

 

[Clifford Bass]

Hi Bruce,

You are welcome!

No, you do not need to be logged in. It will run regardless.

You probably could do something with a timer on a form in Access. But
that requires that Access be up and running the whole time. Unless there
were other considerations, I would not go that route. And there are other
ways that probably would involve programming in some other language. Others
probably have come up solutions to similar needs, but I am not aware of them.
My thought on how to avoid multiple runs would be to add a table that
records the date last run. At the end of the run record the date in the
table. At the beginning of the macro include a check of the date of the last
run compared to the current date. If the same just quit. Otherwise continue
with the append query. When testing you can just adjust the date of the last
run to a prior day.

Hope that helps,

Clifford Bass

 

[Bruce]

Clifford,

Thanks again. I made the date field (posted date) part of the key, therefore
duplications would not be posted.

 

[Clifford Bass]

Hi Bruce,

Sounds like a good and easy solution!

Clifford Bass