0

I need your help to match my fallacy (or just stupidity) by creating a simple SQL Query string in Excel VBA. Connection 3 is a Microsoft SQL database on a separate server. If I use the operator "=" the code work, when using "LIKE" instead runtime error 1004 occur...

This one works fine

With ActiveWorkbook.Connections(3)
        .OLEDBConnection.CommandType = xlCmdSql
        .OLEDBConnection.CommandText = "SELECT * FROM testSystems WHERE **partGroup='CLV'**"
        .Refresh
        
        Debug.Print .OLEDBConnection.CommandText
    End With

This one exceeds in runtime error

With ActiveWorkbook.Connections(3)
        .OLEDBConnection.CommandType = xlCmdSql
        .OLEDBConnection.CommandText = "SELECT * FROM testSystems WHERE **partGroup LIKE 'CLV%'**"
        .Refresh
        
        Debug.Print .OLEDBConnection.CommandText
    End With

I expecting that the LIKE statement works also in this case...

Elwood78
  • 1
  • 1
    Have you tried `LIKE 'CLV*'` (using the asterisk instead of a percent sign) - I'm assuming the asterisks in your code are there because you were thinking that would make those parts bold in this question? – braX Aug 14 '23 at 11:56
  • `%` should work as a wildcard for sql server – Tim Williams Aug 14 '23 at 16:07
  • yes I thried all the wildcards I remember, but it's always the same error. Y You are right, I would make the parts bold, that's not part of my string ;-) The % works with LIKE perfect if I write it directly into the source configuration of the connection – Elwood78 Aug 16 '23 at 08:04
  • What is the text given with the error code? I've been over this a few times, tried myself on an existing connection, with a new connection and no issues with the same syntax so I can't reproduce your issue :/ – Notus_Panda Aug 17 '23 at 14:34
  • Spooky, the only error message is this one I mentioned in my question... runtime error 1004. I solved this problem by using ADODB instead, there the query works well. But I want to understand why this doesn't work with OLEDB... – Elwood78 Aug 22 '23 at 06:50

0 Answers0