MSAccess Query with VBA on Excel Spreadsheets

[lucausa75]

Dear all,

by this function I move the content from an excel spreadsheet to an ADODB recordset:

Public Function RecordSetFromSheet(SheetName As String)
    With Cnx
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With
    Set Cmd.ActiveConnection = Cnx
    Cmd.CommandType = adCmdText
    Cmd.CommandText = "SELECT * FROM [" & SheetName & "$] WHERE [COUNTRY] LIKE '*ITALY*'"
    Rst.CursorLocation = adUseClient
    Rst.CursorType = adOpenDynamic
    Rst.LockType = adLockOptimistic
    Rst.Open Cmd
    Set Rst.ActiveConnection = Nothing
    If CBool(Cmd.State And adStateOpen) = True Then
        Set Cmd = Nothing
    End If
    If CBool(Cnx.State And adStateOpen) = True Then
        Cnx.Close
    End If
    Set Cnx = Nothing
    Set RecordSetFromSheet = Rst
End Function

The issue seems to be related to the below line which does not support the LIKE operator and gives zero record:

Cmd.CommandText = "SELECT * FROM [" & SheetName & "$] WHERE [COUNTRY] LIKE '*ITALY*'"

If I use the equal operator I get the correct result:

Cmd.CommandText = "SELECT * FROM [" & SheetName & "$] WHERE [COUNTRY] = 'ITALY'"

Please, any way to solve the issue?
I need to use the LIKE and NOT LIKE operator

Thanks

 

[Trebor76]

Hi lucausa75,

As your function uses early binding via a reference to ActiveX Data Objects (ADO) you must change the wild card character from * to %. See here for more info.

Regards,

Robert