asked on

Improve processing of data from Oracle to Access

I currently have code that uses a DSN-Less Connection string to update data from Oracle back into Access - The code is run w/i Access. i am looking for a way to speed up the very time consuming processing of the data. Sometimes it will times out or can take longer than 1 hour to complete the process.

I was wondering if I pass a parameter to the Oracle query would this slow things down. if not what do I need to do to my existing code to make this happen? I was hoping to limit the query either by the jobgroupnumber or by the current user's id number.

the current program looks at all records and looks to update those where ProcessInd has changed from S to C.

Karen

Public Sub Update_ProcessInd_SR()

    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim sConn As String
    Dim sSql As String
    
   On Error GoTo Update_ProcessInd_SR_Error
DoCmd.SetWarnings False
'Revised by Kfschaefer on 10/29/07 - due to Oracle Server change to UNIX
     sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY= tcp.world)" & _
             "(PROTOCOL=TCP)(Host=ussedb16.ca.abcnew.com)(Port=1521)))(CONNECT_DATA=(SERVICE_NAME=cmisprod.abcnewdb)));" & _
             "User Id=*****;Password=******;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn

    Set adoRS = New ADODB.Recordset
        
    adoRS.ActiveConnection = adoConn
    adoRS.CursorType = adOpenKeyset
    adoRS.LockType = adLockOptimistic
    adoRS.CursorLocation = adUseServer
    adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR Where "
 
    Do While adoRS.EOF = False
        sSql = "UPDATE TA_SR SET ta_SR.processed_ind = '" & adoRS!PROCESSED_IND & "' WHERE Ta_SR.Job_group = '" & adoRS!Job_Group & "'"
        CurrentDb.Execute sSql, dbFailOnError
            If adoRS!PROCESSED_IND = "C" Then
                sSql = "UPDATE TA_SR SET TA_SR.SubmittedSR = Yes, Processed_ind = '" & adoRS!PROCESSED_IND & "'" & _
                            " , TA_SR.EDDT = Now()" & _
                            " WHERE Ta_SR.Job_group = '" & adoRS!Job_Group & "'"
            DoCmd.RunSQL (sSql)
            End If
            
        adoRS.MoveNext
    Loop
    
    adoRS.Close
    Set adoRS = Nothing
    
      'Now call connect
    
    adoConn.Close
    Set adoConn = Nothing

   On Error GoTo 0
   Exit Sub

Update_ProcessInd_SR_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Update_ProcessInd_SR of Module ModConnectors"
End Sub

Open in new window

slightwv (䄆 Netminder)

Do you need all the columns from CMIS.UDV_RFS_SR?

If not try changing: select *
To just the columns you need: select col1,col2,...

What parameter are you thinking of passing?

Karen Schaefer

ASKER

The UserID - limiting the returned records only for the current user.

k

slightwv (䄆 Netminder)

It will probably help, anything to reduce the amount of data coming back will help performance.

Karen Schaefer

ASKER

Do you know what causes this error:
Picture1.png

slightwv (䄆 Netminder)

Look like the odbc driver provided in the DSN has a problem.

Did you uninstall or install any Oracle products or copy the access database to a different machine w/o an Oracle client?

Karen Schaefer

ASKER

no I actual have both 10 and 11 versions on my machine.

slightwv (䄆 Netminder)

Something has changed from the last time this worked and now.

Launch the odbc admin. And test the DSN.

Karen Schaefer

ASKER

it works correctly - can it be a Office 2007 issue?

slightwv (䄆 Netminder)

Anything is possible. I just wouldn't expect that error to just pop up all of a sudden.

Maybe a reboot?

Michael Vasilevsky

I pefer the below method for DSN-less connections. I don't think it will affect your processing speed but might help with the ODBC call failure.

Sub OracleConnect(strServer As String, strUID As String, strPWD As String)
    Dim ws As Workspace
    Dim db As Database
    Dim LConnect As String, strSQL As String

    On Error GoTo Err_Execute
    
    'Use {Microsoft ODBC for Oracle} ODBC connection
    LConnect = "Driver={Microsoft ODBC for Oracle};Server=" & strServer & ";Uid=" & strUID & ";Pwd=" & strPWD & ";"

    'Point to the current workspace
    Set ws = DBEngine.Workspaces(0)

    'Connect to Oracle
    Set db = ws.OpenDatabase("", False, True, LConnect)

Exit_Execute:
    Exit Sub

Err_Execute:
    MsgBox Err.Description
    Resume Exit_Execute

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

dportas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

dportas:,

after modifying my code to your suggestion - I am getting it can't fine the local version of the CMIS.mdb - Which is not a local file but the Oracle database. hence the adoRS declaration.

here is my latest attempt.

K

Public Sub Update_ProcessInd_SR()

    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
    Dim sConn As String
    Dim strSql As String
    
   On Error GoTo Update_ProcessInd_SR_Error

     sConn = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY= tcp.world)" & _
             "(PROTOCOL=TCP)(Host=abc.ca.abc.com)(Port=1521)))(CONNECT_DATA=(SERVICE_NAME=cmisprod.boeingdb)));" & _
             "User Id=***;Password=***;"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn

    Set adoRS = New ADODB.Recordset
        
    adoRS.ActiveConnection = adoConn
    adoRS.CursorType = adOpenKeyset
    adoRS.LockType = adLockOptimistic
    adoRS.CursorLocation = adUseServer
adoRS!Requestor_ID & "' = gBEMS"
    adoRS.Open "SELECT * FROM CMIS.UDV_RFS_SR WHERE CMIS.UDV_RFS_SR.Requestor_ID = '" & gBEMS & "'"

    Do While adoRS.EOF = False
        'sSql = "UPDATE TA_SR SET ta_SR.processed_ind = '" & adoRS!PROCESSED_IND & "' WHERE Ta_SR.Job_group = '" & adoRS!Job_Group & "'"
        'CurrentDb.Execute sSql, dbFailOnError
            If adoRS!PROCESSED_IND = "C" Then
                'sSql = "UPDATE TA_SR SET TA_SR.SubmittedSR = Yes, Processed_ind = '" & adoRS!PROCESSED_IND & "'" & _
                            " , TA_SR.EDDT = Now()" & _
                            " WHERE Ta_SR.Job_group = '" & adoRS!Job_Group & "'"
            'Debug.Print sSql
          strSQL = "UPDATE TA_SR SET ta_SR.processed_ind =" & _
                    " (SELECT PROCESSED_IND" & _
                    " FROM CMIS.UDV_RFS_SR" & _
                    " WHERE Job_group = Ta_SR.Job_group)" & _
                    " WHERE EXISTS" & _
                    " (SELECT *" & _
                    " FROM CMIS.UDV_RFS_SR" & _
                    " WHERE Job_Group = Ta_SR.Job_Group" & _
                    " AND PROCESSED_IND <> ta_SR.processed_ind)"
            DoCmd.RunSQL (strSQL)
           ' DoCmd.RunSQL (sSql)
            End If
            
        adoRS.MoveNext
    Loop
    
    adoRS.Close
    Set adoRS = Nothing
    
      'Now call connect
    adoConn.Close
    Set adoConn = Nothing

   On Error GoTo 0
   Exit Sub

Update_ProcessInd_SR_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Update_ProcessInd_SR of Module ModConnectors"
End Sub

Open in new window

Karen Schaefer

ASKER

thanks for the great assist.

Karen