Access 2003. Script to run qry at 1630hrs daily

[Guest]

Hi All
If anyone can help out here I would like to know if it's possible to run a
qry within a database at a set time every day.

Would the database need to be open and the script to be written in a module?
Or can a .vbs be set to run at the shceduled time in the Windows Task
Scheduler? If so, would the database need to be open?

Woudl there be a problem running the script if other users were in the
database at the same time?

Thanks
Sally

 

['69 Camaro]

Hi, Sally.

If anyone can help out here I would like to know if it's possible to run a
qry within a database at a set time every day.
Yes.

Would the database need to be open and the script to be written in a
module?

That's one way to do it.

Or can a .vbs be set to run at the shceduled time in the Windows Task
Scheduler?

That's generally the preferred way to do it.

If so, would the database need to be open?

No. The VB Script can open the database.

Woudl there be a problem running the script if other users were in the
database at the same time?

Since it's a multiuser database, you already have the database split, don't
you? If so, then the VB Script opens a copy of the database's front end on
a workstation or server that no one else has open at the same time, so
there's no problem. If not, then you should split your database into a
front end (forms, queries, reports, modules, et cetera) and a back end
(tables and relationships). Place the back end on a shared network server
and a copy of the front end on each user's workstation.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Hi Sally

Another option is to create a macro in Access to run the query and a Quit
action as the macro's last step which will close the database after the query
is run.

Then use Windows scheduler to run the following...

"c:\full path\msaccess.exe /x MyMacroName"

Here, Windows scheduler running Access when you require and the /x parameter
is telling Access to run the macro MyMacroName as soon as the database is
open.

hth

Andy Hull

 

[Guest]

Thanks Andy. I've written the macro and that works fine. I'm hung up though
on the simple part of getting Windows Scheduler to open the database.

If my database is "C:\Test\ABC.mdb" and my macro name is macTestRun and my
path to MSACCESS.exe is "C:\Program Files\Microsoft
Office\OFFICE11\MSAccess.exe"

how do I get it to run this?

Thanks
Sally

 

[Guest]

Hi Andy

I can get Windows Scheduler to open the database if I remove the /x
parameter. So I'm entering something wrong after the /x.

This is what I have:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Documents and
Settings\Sally Green\My Documents\PC Banking.mdb /x macRunPCBanking"

I've tried a variety of things but still no joy.

Appreciate your help.

Thanks
Sally

 

[Guest]

Hi Sally

Put the quotes around the mdb and leave the rest out of the quotes, like...

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Documents and
Settings\Sally Green\My Documents\PC Banking.mdb" /x macRunPCBanking


Andy

 

[Albert D. Kallal]

I give an example vbs script here that runs ms-access

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