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
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