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
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
