Trying to put a variable into MS-Query with Vba

[swallis]

The following code has been adapted from a recorded macro. It works as is, but requires the code to be changed for each new name. I've been trying to make "WHERE (Form2.HORSE='Name')" a variable so I can loop through a list of names and run the query for each. Nearest so far is "WHERE (Form2.HORSE=""'"" & Hrse & ""'"")", which gives me "runtime error 1004 - General ODBC error" and highlights " .Refresh BackgroundQuery:=False". I've tried Hrse as a range and variant.

Any help would be greatly appreciated

Sub MsQry()
Dim Rng As Range, Hrse As Variant
Set Rng = Range("A" & Rows.Count).End(xlUp).Offset(1)
Set Hrse = Range("AQ1")
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Racing\Racing 2016\Racing Form.mdb;DefaultDir=C:\Racing\Racing 2016;DriverId=25;FIL=MS Access;Max" _
        ), Array("BufferSize=2048;PageTimeout=5;")), Destination:=Rng)
        
        .CommandText = Array( _
        "SELECT Form2.HORSE, Form2.FIN, Form2.STR, Form2.MARG, Form2.DATE, Form2.TRACK, Form2.M, Form2.RNO, Form2.PRIZE, Form2.PRWIN, Form2.EVENT, Form2.CLASS, Form2.AGE, Form2.REST, Form2.G, Form2.DIST, Form2" _
        , _
        ".TIME, Form2.SDIST, Form2.STIME, Form2.OP, Form2.MP, Form2.SP, Form2.WGT, Form2.`ALL`, Form2.LIM, Form2.JOCKEY, Form2.BP, Form2.SD, Form2.TW, Form2.EI, Form2.FO, Form2.F1, Form2.OTHER1, Form2.WGT1, Fo" _
        , _
        "rm2.F2, Form2.OTHER2, Form2.WGT2, Form2.F3, Form2.OTHER3, Form2.WGT3, Form2.TRT, Form2.WRT" & Chr(13) & "" & Chr(10) & "FROM `C:\Racing\Racing 2016\Racing Form`.Form2 Form2" & Chr(13) & "" & Chr(10) & "WHERE (Form2.HORSE=""'"" & Hrse & ""'"")" _
        )
        .Name = "Query from MS Access Database_1"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
        
    End With
End Sub

 

[RoryA]

Using Replace(Hrse, "'", "''") should work.

I suggest you manually create a query to your database using MSQuery and filter it to only return a name with an apostrophe in, then see what the SQL looks like for that querytable. It may explain why yours isn't working.

 

[swallis]

OK. I'm not going to try and cover up. Replace does work - I had been looking for a name that is not yet in the database. Stupid and I'm not even sure how it happened. To Jerry and Rory, I'm very, very sorry for wasting your time (and to anyone else looking at it). You spend valuable time helping people and my carelessness has stopped you using it for somebody else. It's a lesson learned and I hope you'll not hold it against me in future.

Steve

 

[Jerry Sullivan]

Steve, No worries. Those kinds of things happen to all of us.

 

[RoryA]

Been there, done worse! Not a problem at all!

 

[swallis]

Thanks to you both.