jsabo
06-09-2015, 10:47 AM
Hey guys,
I have been able to call Oracle through VBA in the past, but for some reason am experiencing a problem now. Here's the code:
Sub Refresh_OTS()
'
' Refresh_OTS Macro
'
'
Application.ScreenUpdating = False
Dim LastRow As Long, recordCount As Long
Dim con As ADODB.Connection
Dim recset As ADODB.Recordset
Dim dbConnectStr As String, SQL_OTS As String
Dim ws As Worksheet
Dim pt As PivotTable
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A2:P" & LastRow).Delete Shift:=xlUp
'ActiveSheet.PivotTables("NSTable").TableRange2.Clear
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
dbConnectStr = "Provider=;User ID=" & ";Password=" & ";Data Source=;"
con.ConnectionString = dbConnectStr
con.Open dbConnectStr
recset.CursorType = adOpenKeyset
recset.LockType = adLockOptimistic
With recset
'SQL_OTS = "SELECT job_number, po_number, tag, tags_seqno, current_promise, PODL_USERDATE1 RTS, CASE WHEN PODL_USERDATE1 <= current_promise + 7 THEN 1 ELSE 0 END pos_on_time, CASE WHEN PODL_USERDATE1 > current_promise + 7 THEN 1 ELSE 0 END pos_late, delivery_reqd, CASE WHEN delivery_actual <= delivery_reqd THEN 1 ELSE 0 END ros_on_time, CASE WHEN delivery_actual > delivery_reqd THEN 1 ELSE 0 END ros_late, delivery_actual, TO_CHAR (delivery_actual, 'MM') AS da_month, TO_CHAR (delivery_actual, 'YYYY') AS da_year, COUNT (*) OVER (PARTITION BY " _
'& "job_number, TO_CHAR (delivery_actual, 'YYYY-MM')) deliveries_this_month, COUNT (*) OVER () total_deliveries FROM po_delivery_lines_v WHERE tags_seqno IS NOT NULL AND tags_seqno <> 0 AND delivery_actual >= ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -12) AND delivery_actual < ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1) AND po_number <> '24590-CM-POA-JP02-00001' ORDER BY delivery_actual DESC, delivery_reqd DESC, po_number, tag;"
SQL_OTS = "SELECT job_number, po_number, tag, tags_seqno, current_promise, PODL_USERDATE1 RTS, CASE WHEN PODL_USERDATE1 <= current_promise + 7 THEN 1 ELSE 0 END pos_on_time, CASE WHEN PODL_USERDATE1 > current_promise + 7 THEN 1 ELSE 0 END pos_late, delivery_reqd, CASE WHEN delivery_actual <= delivery_reqd THEN 1 ELSE 0 END ros_on_time, CASE WHEN delivery_actual > delivery_reqd THEN 1 ELSE 0 END ros_late, delivery_actual, TO_CHAR (delivery_actual, 'MM') AS da_month, TO_CHAR (delivery_actual, 'YYYY') AS da_year, COUNT (*) OVER (PARTITION BY "
SQL_OTS = SQL_OTS + "job_number, TO_CHAR (delivery_actual, 'YYYY-MM')) deliveries_this_month, COUNT (*) OVER () total_deliveries FROM po_delivery_lines_v WHERE tags_seqno IS NOT NULL AND tags_seqno <> 0 AND delivery_actual >= ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -12) AND delivery_actual < ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1) AND po_number <> '24590-CM-POA-JP02-00001' ORDER BY delivery_actual DESC, delivery_reqd DESC, po_number, tag;"
recset.Open Source:=SQL_OTS, ActiveConnection:=con
'Write the field names
For Col = 0 To .Fields.Count - 1
Range("A1").Offset(0, Col).Value = recset.Fields(Col).Name
Next Col
'Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset recset
Dim a As Variant
.MoveFirst
If .recordCount < 1 Then GoTo endnow
.MoveFirst
For Row = 0 To (.recordCount - 1)
.MoveNext
Next Row
End With
On Error GoTo 0
endnow:
Set recset = Nothing
End Sub
When I run the macro, i get an "ORA-00911: invalid character" error and it highlights:
recset.Open Source:=SQL_OTS, ActiveConnection:=con
Any ideas?
JS
I have been able to call Oracle through VBA in the past, but for some reason am experiencing a problem now. Here's the code:
Sub Refresh_OTS()
'
' Refresh_OTS Macro
'
'
Application.ScreenUpdating = False
Dim LastRow As Long, recordCount As Long
Dim con As ADODB.Connection
Dim recset As ADODB.Recordset
Dim dbConnectStr As String, SQL_OTS As String
Dim ws As Worksheet
Dim pt As PivotTable
LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("A2:P" & LastRow).Delete Shift:=xlUp
'ActiveSheet.PivotTables("NSTable").TableRange2.Clear
Set con = New ADODB.Connection
Set recset = New ADODB.Recordset
dbConnectStr = "Provider=;User ID=" & ";Password=" & ";Data Source=;"
con.ConnectionString = dbConnectStr
con.Open dbConnectStr
recset.CursorType = adOpenKeyset
recset.LockType = adLockOptimistic
With recset
'SQL_OTS = "SELECT job_number, po_number, tag, tags_seqno, current_promise, PODL_USERDATE1 RTS, CASE WHEN PODL_USERDATE1 <= current_promise + 7 THEN 1 ELSE 0 END pos_on_time, CASE WHEN PODL_USERDATE1 > current_promise + 7 THEN 1 ELSE 0 END pos_late, delivery_reqd, CASE WHEN delivery_actual <= delivery_reqd THEN 1 ELSE 0 END ros_on_time, CASE WHEN delivery_actual > delivery_reqd THEN 1 ELSE 0 END ros_late, delivery_actual, TO_CHAR (delivery_actual, 'MM') AS da_month, TO_CHAR (delivery_actual, 'YYYY') AS da_year, COUNT (*) OVER (PARTITION BY " _
'& "job_number, TO_CHAR (delivery_actual, 'YYYY-MM')) deliveries_this_month, COUNT (*) OVER () total_deliveries FROM po_delivery_lines_v WHERE tags_seqno IS NOT NULL AND tags_seqno <> 0 AND delivery_actual >= ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -12) AND delivery_actual < ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1) AND po_number <> '24590-CM-POA-JP02-00001' ORDER BY delivery_actual DESC, delivery_reqd DESC, po_number, tag;"
SQL_OTS = "SELECT job_number, po_number, tag, tags_seqno, current_promise, PODL_USERDATE1 RTS, CASE WHEN PODL_USERDATE1 <= current_promise + 7 THEN 1 ELSE 0 END pos_on_time, CASE WHEN PODL_USERDATE1 > current_promise + 7 THEN 1 ELSE 0 END pos_late, delivery_reqd, CASE WHEN delivery_actual <= delivery_reqd THEN 1 ELSE 0 END ros_on_time, CASE WHEN delivery_actual > delivery_reqd THEN 1 ELSE 0 END ros_late, delivery_actual, TO_CHAR (delivery_actual, 'MM') AS da_month, TO_CHAR (delivery_actual, 'YYYY') AS da_year, COUNT (*) OVER (PARTITION BY "
SQL_OTS = SQL_OTS + "job_number, TO_CHAR (delivery_actual, 'YYYY-MM')) deliveries_this_month, COUNT (*) OVER () total_deliveries FROM po_delivery_lines_v WHERE tags_seqno IS NOT NULL AND tags_seqno <> 0 AND delivery_actual >= ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -12) AND delivery_actual < ADD_MONTHS (TRUNC (SYSDATE, 'MM'), 1) AND po_number <> '24590-CM-POA-JP02-00001' ORDER BY delivery_actual DESC, delivery_reqd DESC, po_number, tag;"
recset.Open Source:=SQL_OTS, ActiveConnection:=con
'Write the field names
For Col = 0 To .Fields.Count - 1
Range("A1").Offset(0, Col).Value = recset.Fields(Col).Name
Next Col
'Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset recset
Dim a As Variant
.MoveFirst
If .recordCount < 1 Then GoTo endnow
.MoveFirst
For Row = 0 To (.recordCount - 1)
.MoveNext
Next Row
End With
On Error GoTo 0
endnow:
Set recset = Nothing
End Sub
When I run the macro, i get an "ORA-00911: invalid character" error and it highlights:
recset.Open Source:=SQL_OTS, ActiveConnection:=con
Any ideas?
JS