Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Automate Multiple Append/Update Queries 1

Status
Not open for further replies.

BvCF

MIS
Nov 11, 2006
92
Hopefully, someone can provide some help on this problem

Have the following 5 action queries that need to be performed daily via a scheduler at 9:00 a.m.;

Append inpatient records from Oracle to Access via temp tbl
Append outpatient records from Oracle to Access via temp tbl
Update "AssignedTo" field (via inner join on tbl in Access)
Update various fields on pre-existing records
Update "LagDays" (Date() - RecordCreateDate)

All of the queries above, except for the last one (Update of "LagDays") are performed using a sqlpassthru.

So far, I have created a module in the following format for the updating of certain fields on the local Access table:

CurrentDb.Execute "Delete * FROM tblTemp"
CurrentDb.Executer "Insert into tblTemp (Name,AcctNo,...)
from qrySQLPassThruUpdate;"
Debug.Print tblTemp
CurrentDb.Execute "Update tblAccess Inner Join tblTemp on tblAccess.AcctNo = tblTemp.AcctNo Set ..."

At this point, I am assessing and re-assessing the quickest and logical path to take to automatically run the 5 queries each and every day.

Any insight as to my logic and how I can set the queries to run automatically each and every day?

One thought was to create a macro in the following format and then use Windows Scheduler;

Setwarning off
Open Query (Append IP)
Open Query (Append OP)
Open Query (Update "AssignedTo" field)
Open Query (Update various fields i.e. charges,pymts)
Open Query (Update "LagDays)
Setwarning on

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top