Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Improve processing of data from Oracle to Access

Posted on 2011-10-25
13
Medium Priority
?
339 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Karen Schaefer
13 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37027873
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?
0
 

Author Comment

by:Karen Schaefer
ID: 37027875
The UserID - limiting the returned records only for the current user.

k
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37027927
It will probably help, anything to reduce the amount of data coming back will help performance.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Karen Schaefer
ID: 37028181
Do you know what causes this error:
Picture1.png
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37028198
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?
0
 

Author Comment

by:Karen Schaefer
ID: 37028200
no I actual have  both 10 and 11 versions on my machine.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37028217
Something has changed from the last time this worked and now.

Launch the odbc admin. And test the DSN.
0
 

Author Comment

by:Karen Schaefer
ID: 37028220
it works correctly - can it be a Office 2007 issue?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37028260
Anything is possible.  I just wouldn't expect that error to just pop up all of a sudden.

Maybe a reboot?
0
 
LVL 11

Expert Comment

by:Michael Vasilevsky
ID: 37028573
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

0
 
LVL 22

Accepted Solution

by:
dportas earned 2000 total points
ID: 37029874
It's pretty unusual to need to cursor through a result set and do row-by-row updates like that. Why can't you replace all your code with a single UPDATE statement? Something close to the following ought to be possible and probably much more efficient (note: Code untested. Test it out and make sure you have a backup before you try it on production data.)

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);
0
 

Author Comment

by:Karen Schaefer
ID: 37047314
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

0
 

Author Closing Comment

by:Karen Schaefer
ID: 37469436
thanks for the great assist.

Karen
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

564 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