Getting Oracle tables updated from MS Access via Scheduling Tool
Posted on 2011-10-31
Here is a real puzzler for you.
A little background first.
I have written a program(MS Access) with Oracle Linked tables that updates from Access data into the ORACLE tables.
The code includes accessing table data from various database on various servers(All MS Access), using DSN-Less Oracle connection. My code loops thru a list of ids that determine which server to find the data and processing the update of the Oracle tables 1 at a time. The total processing of this code averages 20 minutes. The current process when executed manually runs perfectly. Then the recently updated Oracle tables are then used to update another Oracle Live database, my program is the intermediate step of creating temporary tables for nightly process. The current process prevents the automation of the updating of the live data.
However, we have attempted to use a Scheduling tool either MS Schedule Task on a different server or used AutoSys on the remote server - with no direct access to the necessary various drives. The nightly process will FAIL when it gets to the Multiple loop of the update of a few of the tables. Some of the tables do get updated and then the process freezes and is force to time out.
Again, when we launch the process manually the code works great. Note we do not have access to a Sql Server so pushing the data to ORACLE from Access is our only option.
Looking for suggestions/solutions - The customer is not happy with the current solution of manually launching the Access/Oracle table update daily.