Setting up queries to run on a schedule

F

Frank

I have some queries that need to run on a regular basis to capture some
operating data. Right now someone logs in and executes the queries on an
hourly basis. Is there a way for me to set them up so they run automatically?
 
B

Barry Gilbert

The way I do this is to create a macro for each job I want to run. In your
case, each macro would run a single query. The macro would have two lines:
OpenQuery
Quit
There is a command line switch that allows you to run a macro when the mdb
opens. You use it like this:
c:\myDatabase.mdb /x myMacroName
In some cases (not sure why), you need to specify the path to Access in
front of your mdb:
c:\Program Files\Microsoft Office\Office\msaccess.exe c:\myDatabase.mdb /x
myMacroName

I find it easiest to put this string into a batch file so I can test it
before setting up the scheduler.

Then, on a server or a PC that's always on, create a scheduled task using
the Windows Task Scheduler. The task runs the batch file.

Don't forget to put the Quit statement in your macro so it exits Access when
done.

Barry
 
F

Frank

Thank you Barry!

This will work for me. However, I have on obstacle I have not found a
solution for. I am connecting to an Oracle Database with an ODBC. I need to
enter a password in order to make a connection. I have not found a command
in the query builder that lets me do this. Is there one? If not is there a
way for me to enter it?
 
B

Barry Gilbert

I'm not sure about Oracle, but you should be able to save the password in
your odbc connection.

Barry
 
F

Frank

would there be a command that would allow me to establish the connection
before I open a query?
 
F

Frank

Barry,

I made the batch file and have that working OK. I also named my macro
'AutoExec' so that when the batch file open the database the macro runs
automatically. I also read hat if I make my query a "Pass Through" query I
program in ODBC connection password. This is partially working. I am now
getting an Oracle error message ORA-00936 - missing expression. Would you
know what could be missing from my query or pass through query setup to cause
this?
 
B

Barry Gilbert

Frank said:
I made the batch file and have that working OK. I also named my macro
'AutoExec' so that when the batch file open the database the macro runs
automatically. I also read hat if I make my query a "Pass Through" query I
program in ODBC connection password. This is partially working. I am now
getting an Oracle error message ORA-00936 - missing expression. Would you
know what could be missing from my query or pass through query setup to cause
this?
Click to expand...

I don't know Oracle, so I'm not familiar with peculiarities of PL/SQL, but
if you could post you SQL code, we'll see if anything looks wrong.

Barry
 
F

Frank

Barry,

Below is my code:

SELECT OMS_CALL.CALL_DATETIME, OMS_CALL.CALL_ID, OMS_CALL.DIST_NO,
OMS_CALL.PREMISE_CUSTNO_ID, OMS_CALL.CUST_NAM, OMS_CALL.CITY_NAM,
OMS_CALL.COMMENT_TEXT, OMS_CALL.CLUE_CD, DateValue([CALL_DATETIME]) AS
[Date], Hour([CALL_DATETIME]) AS [Hour], OMS_CALL.CALL_TYPE_CD,
OMS_CALL.SYSTEM_OPID, OMS_CALL.LAST_CHNG_DATETIME, OMS_CALL.AFF_DATETIME INTO
Calls
FROM OMS_CALL
GROUP BY OMS_CALL.CALL_DATETIME, OMS_CALL.CALL_ID, OMS_CALL.DIST_NO,
OMS_CALL.PREMISE_CUSTNO_ID, OMS_CALL.CUST_NAM, OMS_CALL.CITY_NAM,
OMS_CALL.COMMENT_TEXT, OMS_CALL.CLUE_CD, DateValue([CALL_DATETIME]),
Hour([CALL_DATETIME]), OMS_CALL.CALL_TYPE_CD, OMS_CALL.SYSTEM_OPID,
OMS_CALL.LAST_CHNG_DATETIME, OMS_CALL.AFF_DATETIME
HAVING (((OMS_CALL.CALL_DATETIME) Between #9/1/2006 00:00# And #9/5/2006
23:59#));
 
Top