Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting Oracle tables updated from MS Access via Scheduling Tool

Posted on 2011-10-31
5
Medium Priority
?
335 Views
Last Modified: 2012-08-14
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.

thanks,

K
0
Comment
Question by:Karen Schaefer
  • 3
5 Comments
 
LVL 85
ID: 37058083
My first thought would be that the account you're using for the automated process does not have sufficient permissions to work with the Oracle tables (i.e. it cannot log on to the Oracle machine, or it can log on but doesn't have permission to update the table, etc etc).

You also mention "on a different server" - can you not run the scheduler on the machine where you KNOW the process works?
0
 

Author Comment

by:Karen Schaefer
ID: 37058406
We have confirmed the user's permissions/access are set correctly.  The Autosys will execute the activation of ACCESS and Access will start and then update some of the tables correctly, but then when it comes to updating the tables that use the code that looks at the datasource from other servers/pc's is when it stop the processing and either will lock up or time out on the processing of the data.

The code in Access runs very smoothly when it is launched manually via the autoexec.  I have posted the code that works great manually but when the AutoSys attempts to execute this code - fails.  We even changed the server directory sources from a drive letter back to the UNC, hence the nFP variable set as a CONST with the default UNC.

"You also mention "on a different server" - can you not run the scheduler on the machine where you KNOW the process works? "  - No the Autosys is not set up to run Office and the other method is not company wide standard - and we need to ween ourselves away from that processs.

any suggestion,

Karen

Do you know of a better way to get the Access data into the Oracle tables - I suggested that instead of pushing the data that we pull the data into Oracle, however, we don't have access to a SQL Server.
' Purpose   : Creates a Union Query for all applicable AP for the TA_FTIR Table - Updating the ORACLE tables
'----------------------------------------------------------------------------------------------------------------
'
Public Function GenerateFTIR()
   Dim strSql As String
   Dim strSql1 As String
   Dim strSql2 As String
   Dim strSqlAP As String
   Dim rs As DAO.Recordset
   Dim gApNO As String
   Dim strFile As String
   
   On Error GoTo GenerateFTIR_Error
    n = 0

    strSql = ""
    strSql1 = ""
    strSqlAP = "SELECT TA_AIRPLANEINFO.APNO, TA_AIRPLANEINFO.APDBMS_DB, ApMajMdl, APMNRMDL" & _
                " FROM ACTIVE_AIRPLANES INNER JOIN TA_AIRPLANEINFO ON" & _
                " ACTIVE_AIRPLANES.AIRPLANENBR = TA_AIRPLANEINFO.APNO"
            
    Set rs = CurrentDb.OpenRecordset(strSqlAP)
    
        gApNO = rs.Fields("APNO")
        strFile = nFP & Right(rs.Fields("APDBMS_DB"), Len(rs.Fields("APDBMS_DB")) - 3)
        rs.MoveFirst
        Do Until rs.EOF
            gApNO = rs.Fields("APNO")
            strFile = nFP & Right(rs.Fields("APDBMS_DB"), Len(rs.Fields("APDBMS_DB")) - 3)
            
            strSql = "INSERT INTO TA_FTIR ( SRC_FILE, FTIR_MeasNo, FTIR_APNO, FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS," & _
                        " FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR," & _
                        " FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU, FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT," & _
                        " FTIR_INSTLDWGNO, LAST_REV_ID, LAST_REV_DATE, DTAPPNMEASNO, DTUPDTMEASNO )" & _
                    " SELECT " & Chr(39) & gApNO & Chr(39) & " as SRC_FILE, FTIR_MeasNo, " & Chr(39) & gApNO & Chr(39) & " as FTIR_APNO," & _
                        " FTIR_TITLE, FTIR_RM, FTIR_SPS, FTIR_MIN, FTIR_MAX, FTIR_UNITS, FTIR_ACC, FTIR_TYPE, FTIR_LOC, FTIR_FLTR," & _
                        " FTIR_HCO, FTIR_LCO, FTIR_BUSNAME, FTIR_SU, FTIR_EU_SU, FTIR_EU, FTIR_SPHDL, FTIR_DESC, FTIR_MAINTCD, FTIR_CMT," & _
                        " FTIR_INSTLDWGNO, LAST_REV_ID, LAST_REV_DATE, DTAPPNMEASNO, DTUPDTMEASNO" & _
                    " FROM TA_FTIR IN '" & strFile & "'"
            CurrentDb.Execute (strSql)
        rs.MoveNext
        Loop
    
rs.Close
Set rs = Nothing
rs1.Close
Set rs1 = Nothing

   On Error GoTo 0
   Exit Function

GenerateFTIR_Error:
    On Error Resume Next
    n = 1
End Function

Open in new window

0
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 37063613
Oracle Transparent Gateway would allow to access Excel from Oracle itself. But that needs the Jet Drivers located on a machine running at least an Oracle Listener and having the TG files installed (or copied), and some setup effort.

Whatsoever, besides (maybe) using a server-based cursor locking up because of other processes accessing and changing tables the same time, or some missing commits to release locks, I cannot see a reason. But that might also result from us not having any insight into your linked tables used. In particular it totally obscurred where Oracle comes into play, and which part is Access only.
0
 

Author Comment

by:Karen Schaefer
ID: 37063975
thanks for all the input, we are currently playing with the time of day that the process launches, the original was launching at 4 a.m. and seemed to be hanging.  It was ran this morning at 8 a.m. and it seemed to work correctly using the AutoSys method.  We plan to keep it there for now and see how the rest of the week plays out.  I will keep you informed as to the progress.

Thanks,

Karen
0
 

Author Closing Comment

by:Karen Schaefer
ID: 37606925
Thanks for the great assist
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question