Setting up queries to run on a schedule

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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?
 
I'm not sure about Oracle, but you should be able to save the password in
your odbc connection.

Barry
 
would there be a command that would allow me to establish the connection
before I open a query?
 
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?
 
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
 
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#));
 
Back
Top