SQL Query with Excel VBA Returns General ODBC Error when Using Variables

I have set up an SQL query of an Excel Worksheet using VBA in Excel. The query works when I "hardcode" the search string, but when I use a variable, I get the following error:

Run Time Error: '1004'
General ODBC Error.

Here is the code with the variable xCaseNo.

Sub CreateQT()

    Dim sConn As String
    Dim sSql As String
    Dim oQt As QueryTable
    Dim WS As Worksheet
    Dim xCaseNo As String

 xCaseNo = "123456"   
sConn = "ODBC;DSN=Excel Files;DBQ=C:\Users\xxxx\Dropbox\Book1.xlsm;DefaultDir=C:\Users\xxxx\Dropbox;_
DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
sSql = "SELECT `Clients$`.LastName, `Agents$`.LastName, `Agents$`.SerialNo, _
`Agents$`.CaseNo FROM `Clients$` `Clients$`, `Agents$` `Agents$` _
WHERE (`Agents`.CaseNo=xCaseNo);"
    
Set oQt = Application.ActiveSheet.QueryTables.Add(Connection:=sConn, Destination:=Range("a1"), Sql:=sSql)
    oQt.Refresh

End Sub

If I supply the CaseNo by typing it in a string enclosed in single quotes, the query works, as in:

sSql = "SELECT `Clients$`.LastName, `Agents$`.LastName, `Agents$`.SerialNo,_
 `Agents$`.CaseNo FROM `Clients$` `Clients$`, `Agents$` `Agents$` WHERE _
(`Agents`.CaseNo='123456');"

When I get the error message the oQt.Refresh line is highlighted in yellow.

I suspected the problem may be related to SQL requiring quotes around the search string, so I defined xCaseNo as

xCaseNo = (Chr(39) & xCaseNo & Chr(39))

, but that didn't work either.

Thanks.