PDA

View Full Version : [SOLVED] ADODB Connection Problem?



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

Kenneth Hobs
06-09-2015, 11:50 AM
Looks like you have (1) a connection issue or (2) an SQL syntax issue. For (1), look at the various connection strings used in the links here. http://lmgtfy.com/?q=excel+ado+oracle+site%3Avbaexpress.com
or, http://www.connectionstrings.com/oracle/.

For (2), make the SQL string simple and then tests each part: "SELECT * FROM MyYourTableNameHere".

When building SQL strings, I usually like to build them manually through Excel or Access built-in methods first.

jsabo
06-09-2015, 12:23 PM
Fixed. It was as easy as taking out the semi-colon at the end of the SQL.