Oracle SQL Looping VBA for number not working

Question

So we have a simple query where we we can format the column of 8 digit numbers for the CRN value that works fine, but if place the numbers in an excel sheet and try to get a looping query in VBA to run on them we get no result.

What am I doing wrong or missing?


Dim DB_CONNECTION           As ADODB.Connection
Dim DB_RECORDSET            As ADODB.Recordset
Dim CELLADDRESS             As Variant
Dim CRN                     As Variant
Dim ANSWER                  As String
Dim SQL_STRING              As String

Set DB_CONNECTION = New ADODB.Connection
Set DB_RECORDSET = New ADODB.Recordset



DB_CONNECTION.ConnectionString = "Driver={Oracle in OraClient18Home1_32bit}; Dbq=XXXXXX; Uid=XXXXX; Pwd=XXXXXX;QTO=F;"

DB_CONNECTION.Open

Sheets("Data").Activate

Range("A6").Select

Do Until IsEmpty(ActiveCell)

CELLADDRESS = ActiveCell.Address
CRN = ActiveCell.Value

SQL_STRING = ""
SQL_STRING = SQL_STRING + " SELECT CUSTIMA.BCUSTCLS.U##CLASS_CODEC"
SQL_STRING = SQL_STRING + " FROM CUSTIMA.BCUSTCLS"
SQL_STRING = SQL_STRING + " WHERE CUSTIMA.BCUSTCLS.U##CUST_REF = 'CRN'"
SQL_STRING = SQL_STRING + " AND CUSTIMA.BCUSTCLS.U##CLASS_CODEC = 'DO' "

DB_RECORDSET.Open SQL_STRING, DB_CONNECTION

Sheets("Data").Range(CELLADDRESS).Offset(0, 2).CopyFromRecordset DB_RECORDSET

        DB_RECORDSET.Close


        ActiveCell.Offset(1, 0).Select

Loop
End Sub

You are facing the same exact issue as your [previous question](https://stackoverflow.com/q/75622847/1422451) of which I answered with the best practice of SQL parameterization. `CRN` is a VBA variable and will not be recognized in your SQL and hence no result. Either concatenate (`...U##CUST_REF = '" + CRN + "'"`) or avoid quotes with parameterization. — Parfait, Mar 23 '23 at 00:33

score 2 · Accepted Answer · answered Mar 23 '23 at 02:16

Something like this should work:

Sub RunQueries()
    Dim DB_CONNECTION As ADODB.Connection, DB_RECORDSET As ADODB.Recordset
    Dim c As Range, SQL_STRING As String
    
    Set DB_CONNECTION = New ADODB.Connection
    Set DB_RECORDSET = New ADODB.Recordset
    DB_CONNECTION.ConnectionString = "Driver={Oracle in OraClient18Home1_32bit}; " & _
                                     "Dbq=XXXXXX; Uid=XXXXX; Pwd=XXXXXX;QTO=F;"
    DB_CONNECTION.Open
    
    Set c = ThisWorkbook.Sheets("Data").Range("A6")
    
    Do While Len(c.Value) > 0
    
        SQL_STRING = " SELECT CUSTIMA.BCUSTCLS.U##CLASS_CODEC " & _
                     " FROM CUSTIMA.BCUSTCLS " & _
                     " WHERE CUSTIMA.BCUSTCLS.U##CUST_REF = '" & c.Value & "' " & _
                     " AND CUSTIMA.BCUSTCLS.U##CLASS_CODEC = 'DO' "
    
        DB_RECORDSET.Open SQL_STRING, DB_CONNECTION
        If Not DB_RECORDSET.EOF Then
            c.Offset(0, 2).Value = DB_RECORDSET.Fields(0).Value
        Else
            c.Offset(0, 2).Value = "{no match}"
        End If
        
        DB_RECORDSET.Close
        Set c = c.Offset(1)
    Loop
End Sub

Thank you. Just couldn't get my head around why the format for another query worked so well for that but did nothing for this. Very much appreciated and I will probably be stealing this format for everything else like this now :) — Vukoneti, Mar 23 '23 at 03:06

Oracle SQL Looping VBA for number not working

1 Answers1

Linked