VBA Pass-Through SQL to Oracle database (1 Viewer)

frozbie

Occasional Access Wizard
Joined
Apr 4, 2005
Messages
52
Hi,

I'm automating an export of Oracle data into MS Access for a business process. The export runs twice, once for one set of tables in one database and then for a second set of identically structured tables in another database.

The export works fine for one set of tables but not the other and I'm posting in case anyone has encountered a similar situation and can advise how to resolve.

The weird thing is that the SQL being passed through to Oracle runs for both sets of tables if run in SQL Developer. VBA Code below. I've modified to change table names etc.

I've tried running on a colleagues PC and same issue. One other thing, when I initially ran this code this morning, neither SQL query worked. But after I had run through SQL Developer, the VBA code ran for the first set.

Also, I've tried copying the code and changing to just run for the second set of tables but that failed as well.

I have a work around as I can manually run the SQL to extract the data but would be great to find why it fails on one set of tables and not the other.

Mark

Code:
Sub ImportValues()
    '// pull in oracle values
    
    Dim oconn As New ADODB.connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String

    Dim strDay As String
    
    Dim dbr As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim x, y As Long
    Dim dtStart As Date
    
    Dim arrSets(1) As String
    
    '//Delete table data prior to importing new data.
    If fnctDeleteTabledata("Oracle_Values") = False Then
        MsgBox ("Could not delete data in table Oracle_Values")
        Exit Sub
    End If
    
    '//Define SQL query
    If Weekday(Now, vbMonday) = 1 Then
        strDay = Format(Now - 3, "DD-MMM-YYYY")
    Else
        strDay = Format(Now - 1, "DD-MMM-YYYY")
    End If
    
    arrSets(0) = "SetOne"
    arrSets(1) = "SetTwo"
    
    For y = 0 To 1
    '// Record when process starts for audit table
    dtStart = Format(Now, "HH:NN:SS")
    
    
    '//remove in live!
    strDay = "11-Sep-11"

'// Following works for SetOne but not SetTwo - returning no rows. Just getting EOF

strSQL = "SELECT P.SYS_CDE, P.CMPNT, S.TYPE, " _
    & "M.ST, M.AC_NBR, " _
    & "SUM(CASE P.CMPNT WHEN 'AA' THEN S.AMT WHEN 'BB' THEN S.AMT WHEN 'CC' THEN S.AMT ELSE CASE WHEN S.AMT < 0 THEN S.AMT * -1 ELSE S.AMT END END) AMT_RESOLVED " _
    & vbCrLf & "FROM " & arrSets(y) & "1.SPT S, " & arrSets(y) & "1.RL R, " & arrSets(y) & "1.PRD P, " _
    & arrSets(y) & "1.MAP M, " & arrSets(y) & "1.AIR A, " & arrSets(y) & "1.NK N " _
    & "AND (S.DTE) = '" & strDay & "' " _
    & "AND S.ID = R.ID AND S.ID = M.ID " _
    & "AND P.SYS_CDE = M.SYS_CDE " _
    & "AND R.P_CDE = P.P_ID " _
    & "AND P.SYS_CDE =  'EE' AND R.ID = A.ID " _
    & "AND A.end_dte between R.start_dte and R.end_dte " _
    & "and A.id = N.id (+) " _
    & "and A.end_dte > '" & strDay & "' " _
    & vbCrLf & "GROUP BY P.SYS_CDE, P.CMPNT, S.TYPE, M.ST , M.AC_NBR " _
    & "ORDER BY P.CMPNT"

    
    Debug.Print strSQL
    
    'Establish connection to Oracle
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    strConn = "Provider=OraOLEDB.Oracle;Data Source=Oracle1;User Id=user;Password=pass;"
    Set oconn = New ADODB.connection
    oconn.connectionString = strConn
    oconn.Open
    Set rs = oconn.Execute(strSQL, adCmdText)
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    'if no records returned then advise user else
    If rs.EOF Then
        MsgBox ("No data has been returned from Oracle for " & arrSets(y) & "!")
    Else
        
        'write data to table Values
        Set dbr = CurrentDb
        Set rst = dbr.OpenRecordset("Oracle_Values")
        
        x = 1
        
        rs.MoveFirst
        Do While rs.EOF = False
        
        With rst
            .AddNew
            rst("SET").Value = arrSets(y)
            rst("SYS_CDE").Value = rs.Fields("SYS_CDE")
            rst("CMPNT").Value = rs.Fields("CMPNT")
            rst("TYPE").Value = rs.Fields("TYPE")
            rst("NBR").Value = rs.Fields("NBR")
            rst("AMT_RESOLVED").Value = rs.Fields("AMT_RESOLVED")
            .Update
        End With
        
        rs.MoveNext
        
        Debug.Print x
        x = x + 1
        
        Loop
    End If
    
    '// write to audit table
    Call Insert_Audit_Table(dtStart, Format(Now, "HH:NN:SS"), strSQL, "tbl_Audit_SQL")
    
    Set rs = Nothing
    Set oconn = Nothing
    
    Next y
        
    MsgBox ("Oracle SQL completed")
    
End Sub

Function fnctDeleteTabledata(strTBLname As String) As Boolean
'// Tested and works.
'// Use in Live

On Error GoTo HandleErr
    
    Dim db As DAO.Database
    
    Set db = CurrentDb()
    
    db.Execute "DELETE * FROM " & strTBLname
    
    db.Close
       
    fnctDeleteTabledata = True
    
' Exit Here if no problems
Exit Function
    
HandleErr:
    fnctDeleteTabledata = False
    
End Function


Sub Insert_Audit_Table(dtStart As Date, dtEnd As Date, strSQL As String, strTable As String)
'// Tested - include in Live
'// Writes to audit tables

    Dim dbr As DAO.Database
    Dim rst As DAO.Recordset

    Set dbr = CurrentDb
    Set rst = dbr.OpenRecordset(strTable)

    With rst
        .AddNew
        rst("Date").Value = Format(Now, "DD-MMM-YYYY")
        rst("Start_Time").Value = dtStart
        rst("End_Time").Value = dtEnd
        rst("Details").Value = strSQL
    .Update
    End With

End Sub
 
Hi Michael,

Thanks for your reply.

I'm not receiving an error. The connection appears to open correctly for both iterations and for the first set returns rows but for the second set does not. I capture this by checking for EOF

It is conceivable that there is an error with one of the connections at the oracle end which I need to identify though.

I'm currently exploring whether the user name I have is valid for both Oracle databases being accessed; also whether there is a timeout problem.

I colleague suggested I just Union join both query's rather than running two iterations...

I'll update with progress.

Mark
 
I colleague suggested I just Union join both query's rather than running two iterations...

So both queries are running against the same server? I got the impression you were talking about two different servers with the same DB schema on each.
 
Hi Michael,
As far as I'm aware... Something I'll need to check with our IT department.
They are both within the same data warehouse system anyway and both visible within the Oracle schema.
Mark
 
Update as of 6 October.

I've narrowed issue down to two columns on the second database. If i don't include them in the where clause, I get records returned. Unfortunately these are crucial columns to filter on.

Both columns are date datatype. I've checked in the database schemas and the schema is identical across both databases.

I'm currently exploring using a UNION query but initial attempts are failing to return any rows from either database. (Again - running union query directly through SQL Developer and I get rows returned from both databases)

:banghead:

Keep returning to the fact that the SQL is okay because I can pull data back - just not via pass through from access. I guess next stop is to get DBA to tell me what is actually being received by server and see if there is any difference there.

Mark
 
Well, good progress at least that you know which columns to focus on. ;)

Perhaps redesign the query code to utilize...

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Perhaps the date datatype is getting munged in the various connection hops between Access / ODBC / SQL BE DB.

Use that sample code, and simply swap your working ADO.Connection object for the sample code binding to the Access FE DB.
 

Users who are viewing this thread

Top Bottom