Problem with ODBC call via ADO

[oni29]

Hi,

Below is the Excel 2002 VBA code that I am using. FGBTS-prod is an ODBC connection to an Access 2002 database.

Public Function ADODBtest(numOrder As Long, Optional strFilter As String) As String

    Dim rsData As ADODB.Recordset
    Dim szConnect As String
    Dim szSQL As String

    On Error GoTo ADODBtest_error

    szConnect = "Provider=MSDASQL;DSN=FGBTS-prod;"
   
    szSQL = "select CIRCUIT_NAME from tblCircuits where ORDER_NUMBER = " & numOrder
    
    If Len(strFilter) > 0 Then
        szSQL = szSQL & " and CIRCUIT_NAME like " & Chr(39) & strFilter & Chr(39)
    End If

    szSQL = szSQL & Chr(59)

    Debug.Print szSQL

    Set rsData = New ADODB.Recordset
    rsData.Open szSQL, szConnect, adOpenStatic, _
                adLockReadOnly, adCmdText

    If rsData.RecordCount > 1 Then
        If Len(strFilter) > 0 Then
            strReturn = "Error - Multiple records returned, please refine filter"
        Else
            strReturn = "Error - Multiple records returned, please specify a filter"
        End If
        GoTo ADODBtest_end
    End If
   
    If rsData.RecordCount = 0 Then
        strReturn = "Error - No records returned"
        GoTo ADODBtest_end
    End If
   
    strReturn = rsData.Fields(0).Value
    
    GoTo ADODBtest_end

ADODBtest_error:
    Debug.Print "Error: " & Err.Number & " " & Err.Description & " " & Err.Source

ADODBtest_end:

    'clean up
    rsData.Close
    Set rst = Nothing
    ADODBtest = strReturn

End Function

My problem with the above code is:

* If the function is called with just the numOrder parameter it works fine.
* If it is called with both parameters it returns no records (rsData.RecordCount = 0). An example of the SQL this produces is below:

select CIRCUIT_NAME from tblCircuits where ORDER_NUMBER = 613888 and CIRCUIT_NAME like '*AL*';

The Access database runs this query fine & returns the correct value. Any idea why it won't work from the VBA code?

 

[pbaldy]

Try % instead of * as the wildcard (an ADO thing)