Extracting Data from ERP Database based on 2 variables in Spreadsheet...cont'd

This is what I copy from Immediate Window

SELECT WORKORDER_BASE_ID, RESOURCE_ID, SEQUENCE_NO, USER_1, USER_2, USER_3, USER_4, USER_5,USER_6, USER_7, USER_8, USER_9, USER_10, CONCAT(CONCAT(WORKORDER_BASE_ID,':'),SEQUENCE_NO) AS "TEMPKEY_ID" FROM OPERATION WHERE 1=1 AND WORKORDER_TYPE = 'M' AND WORKORDER_BASE_ID In ('') AND SEQUENCE_NO In ('')

Is something missing in the "In" strings?

Also it gives Oracle error stating FROM keyword is not where it is expected to be

Yes, the strIDs are completely blank - I've sent you a PM - off for some food - will check back later.

Ok, for sake of proper thread closure, this has been solved. DonkeyOte has helped me immensely and I apologize to him for the extra hair he might of lost on this one...

Here is the final working code:

Option Explicit
Sub GetDataFromVisual_DO_Last()
'--------------------------------------------------------------------------------------------------------------------------------------
'Defining variables
'--------------------------------------------------------------------------------------------------------------------------------------
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim vrngData As Range, vrngChunkData As Range, vrngSubData As Range, c As Range
Dim db_name As String, UserName As String, Password As String, vstrIDs As String, wstrIDs As String, strSQL As String, strKey As String
Dim XLCalc As XlCalculation: XLCalc = Application.Calculation
Dim i As Integer
Const BLOCK_SIZE = 1000
'--------------------------------------------------------------------------------------------------------------------------------------
'Disable Screen Refresh & Events & Alter Calc to Manual
'--------------------------------------------------------------------------------------------------------------------------------------
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

'--------------------------------------------------------------------------------------------------------------------------------------
'Establish Database Connection & Recordset
'--------------------------------------------------------------------------------------------------------------------------------------
Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset
db_name = "SANDBOX_ODBC"
UserName = "RPRO"
Password = "PASSWORD"
cnOra.Open "DSN=" + db_name + ";UID=" + UserName + ";PWD=" & Password + ";"
rsOra.CursorLocation = adUseServer
'--------------------------------------------------------------------------------------------------------------------------------------
'Set Handler... risky...
'--------------------------------------------------------------------------------------------------------------------------------------
On Error Resume Next
'--------------------------------------------------------------------------------------------------------------------------------------
'Work Order Ranges
'--------------------------------------------------------------------------------------------------------------------------------------
Set vrngData = Range("C7", Cells(Rows.Count, "C").End(xlUp))
Set vrngChunkData = vrngData.Offset(0, 0).Resize(BLOCK_SIZE)
Set vrngSubData = Intersect(vrngData, vrngChunkData)
'--------------------------------------------------------------------------------------------------------------------------------------
'Loop Subset of vrngData
'--------------------------------------------------------------------------------------------------------------------------------------
Do While Not vrngSubData Is Nothing
    'Debug.Print vrngSubData.Address
    With vrngSubData
        vstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",MID(" & .Address & ",2,5),"" "")")), " ")
        vstrIDs = "'" & Replace(Application.WorksheetFunction.Trim(vstrIDs), " ", "','") & "'"
        wstrIDs = Join(Application.Transpose(Evaluate("IF(" & .Address & "<>"""",TRIM(MID(" & .Address & ",FIND(""^^"",SUBSTITUTE(" & .Address & ",""-"",""^^"",LEN(" & .Address & ")-LEN(SUBSTITUTE(" & .Address & ",""-"",""""))))+1,3)),"" "")")), " ")
        wstrIDs = "'" & Replace(Application.WorksheetFunction.Trim(wstrIDs), " ", "','") & "'"
    End With
    'Compile query to get Costs for PartIDs in the strIDs array
    strSQL = "SELECT "
        strSQL = strSQL & "WORKORDER_BASE_ID, RESOURCE_ID, SEQUENCE_NO, USER_1, USER_2, USER_3, USER_4, USER_5,USER_6, USER_7, USER_8, USER_9, USER_10, "
        strSQL = strSQL & "CONCAT(CONCAT(WORKORDER_BASE_ID,':'),SEQUENCE_NO) AS ""TEMPKEY_ID"" "
        'strSQL = strSQL & "TEMPKEY_ID " 'MYSQL MODIFICATION (had to create fixed field rather than generate @ run time)
    strSQL = strSQL & "FROM "
        strSQL = strSQL & "OPERATION "
    strSQL = strSQL & "WHERE 1=1 "
        strSQL = strSQL & "AND WORKORDER_TYPE = 'M' "
        strSQL = strSQL & "AND WORKORDER_BASE_ID In (" & vstrIDs & ") "
        strSQL = strSQL & "AND SEQUENCE_NO In (" & wstrIDs & ") "
    Debug.Print strSQL
    'Execute SQL
    rsOra.Open strSQL, cnOra, adOpenStatic
    
    'Clear Col AI
    Range(Cells(2, "AB"), Cells(Rows.Count, "AL")).ClearContents
    
 
    'Iterate Subset of vrngData and find matching record in Recordset (if exists)
    For Each c In vrngSubData
        If c.Value <> "" Then
            strKey = "'" & c.Offset(, 13).Value & ":" & c.Offset(, 14).Value & "'"
            c.Offset(0, 25).Value = strKey
            With rsOra
                .Find "TEMPKEY_ID = '" & Mid(c.Value, 2, 5) & ":" & Trim(Mid(c.Value, 8, 3)) & "'"
                If Not .EOF Then
                    c.Offset(0, 26).Value = rsOra![USER_1]
                    c.Offset(0, 27).Value = rsOra![USER_2]
                    c.Offset(0, 28).Value = rsOra![USER_3]
                    c.Offset(0, 29).Value = rsOra![USER_4]
                    c.Offset(0, 30).Value = rsOra![USER_5]
                    c.Offset(0, 31).Value = rsOra![USER_6]
                    c.Offset(0, 32).Value = rsOra![USER_7]
                    c.Offset(0, 33).Value = rsOra![USER_8]
                    c.Offset(0, 34).Value = rsOra![USER_9]
                    c.Offset(0, 35).Value = rsOra![USER_10]
                    'Debug.Print c.Offset(0, 26).Value
                End If
               .MoveFirst
            End With
        End If
    Next c
    'Close Recordset
    rsOra.Close
    'Redefine Ranges Before Proceeding
    Set vrngChunkData = vrngSubData.Offset(vrngSubData.Rows.Count).Resize(BLOCK_SIZE)
    Set vrngSubData = Intersect(vrngData, vrngChunkData)
Loop
'--------------------------------------------------------------------------------------------------------------------------------------
' Close
'--------------------------------------------------------------------------------------------------------------------------------------
cnOra.Close
'--------------------------------------------------------------------------------------------------------------------------------------
' Release
'--------------------------------------------------------------------------------------------------------------------------------------
Set vrngData = Nothing
Set vrngChunkData = Nothing
Set vrngSubData = Nothing
'--------------------------------------------------------------------------------------------------------------------------------------
'Restore App Level Settings
'--------------------------------------------------------------------------------------------------------------------------------------
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = XLCalc
End With
'--------------------------------------------------------------------------------------------------------------------------------------
'END
'--------------------------------------------------------------------------------------------------------------------------------------
Exit Sub

End Sub

Thanks DonkeyOte !

NBVC, as discussed I think there is still one line you need to update, ie the below:

.Find "TEMPKEY_ID = '" & Mid(c.Value, 2, 5) & ":" & Trim(Mid(c.Value, 8, 3)) & "'"

needs to be modified in line with the revisions to wstrID creation.