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

 

[Jerry Sullivan]

Hi Steve,

Try modifying that part to read:

  "WHERE (Form2.HORSE='" & Hrse & "')" _

 

[swallis]

Works like magic. Thank you Jerry.

 

[swallis]

Well it did work like magic until I tried to include names with apostrophes in them. I've read somewhere that you can't have an Apostrophe in any word enclosed by Apostrophes.

Is there a way around this?

Below is the code used to change the name and call the above procedure (no laughing please):

Sub LoopyLoo()
Dim t As Single
t = Timer
Application.ScreenUpdating = False

Dim RngA, celA As Range
Dim I As Variant
Set RngA = Range("ar2", Range("ar" & Rows.Count).End(xlUp))
Set I = Rows(1)
Set celA = Range("aq1")
For Each I In RngA
     If celA.Value <> I.Value Then celA.Value = I.Value
     Call MsQry
Next I

Application.ScreenUpdating = True
MsgBox Timer - t
End Sub

 

[swallis]

If I add the line,

Hrse = Replace(Hrse, "'", "''")

the code runs but doesn't return data for the name with an apostrophe. If I dim Hrse as String (which I think I should be doing??), it highlights Hrse in the line

Set Hrse = Range("AQ1")

and Tells me "Compile Error. Object required"

Any help, please.

 

[swallis]

More searching and it seems "set" not required for String. That now works, but is still not returning data for the relevant name.

Could it be a problem with Access not reading double apostrophes as single? if so how do I get around it?

Also as an aside this "Replace" code seems to have increased search time by 50%.

 

[Jerry Sullivan]

Steve, In MS Query, you can use square brackets instead of apostrophes to wrap field names that have spaces or special characters.

Try...

   "WHERE (Form2.HORSE=[" & Hrse & "])" _

 

[swallis]

Thanks Jerry. That just gives Runtime error 1004 - general ODBC. Even when I take out the apostrophe.

 

[Jerry Sullivan]

I'm able to use code similar to yours that works with brackets around a field name that includes an apostrophe.

You might have a typo or syntax error. Please post the entire code you are trying that is generating an error.

 

[swallis]

Jerry, this is the code that gives the OBDC error. If I replace The square brackets with your first suggestion - "WHERE (Form2.HORSE='" & Hrse & "')" - it works if there are no apostrophes in the list of names or if I add the following code: Hrse = Replace(Hrse, "'", "''"), but doesn't copy data across for the names with apostrophes.

Thanks for looking at this.

Sub LoopyLoo()
Dim t As Single
t = Timer
Application.ScreenUpdating = False

Dim RngA, celA As Range
Dim I As Variant
Set RngA = Range("ar2", Range("ar" & Rows.Count).End(xlUp))
Set I = Rows(1)
Set celA = Range("aq1")
For Each I In RngA
     If celA.Value <> I.Value Then celA.Value = I.Value
     Call MsQry
Next I

Application.ScreenUpdating = True
MsgBox Timer - t
End Sub


Sub MsQry()
Dim Rng As Range, Hrse As String
Set Rng = Range("A" & Rows.Count).End(xlUp).Offset(1)
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