0

I have the following sub and I want to perform SQL case sensitive statement but it failed. Do you have any idea I can fix this? Or does ADODB support collation in a statement?

Environment is Excel 2016 and VBA

"select * from saor where rep = 'jones' collate LATIN1_GENERAL_CS_AS"

.

Sub run_sql()
    Dim cn As Object, rs As Object

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Chr(39) & _
    ThisWorkbook.FullName & Chr(39) _
& ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
        .Open
    End With

    Set rs = cn.Execute("select * from saor where rep = 'jones' collate LATIN1_GENERAL_CS_AS")
    Sheet2.UsedRange.Clear
    For i = 0 To rs.Fields.Count - 1
        Sheet2.Cells(1, i + 1).Value = rs.Fields(i).Name
    Next i

    Sheet2.Range("A2").CopyFromRecordset rs

    rs.Close
    cn.Close
    Set cn = Nothing
    Set rs = Nothing

End Sub
Community
  • 1
  • 1
Đức Thanh Nguyễn
  • 9,127
  • 3
  • 21
  • 27

0 Answers0