Using a variable in sql query

[John Sh]

I have scanned the net and got so may different answers, that don't work, to this question'
I am trying to replace the "From" clause with a variable in the code below.
I keep getting an error in the from clause no matter what variations I try.

Private Sub Command100_Click()
    Dim sTable  As String
    Dim sCommon As String
    Dim rs      As Recordset
    Dim str     As String
    Set rs = oDB.OpenRecordset("TableList", dbOpenSnapshot)
    With rs
        .MoveFirst
        Do
            sTable = !TableName
'            sCommon = !CommonName
            str = "INSERT INTO tempfamily ( Family, Infra, BoxNo, BayNo, ShelfNo, Collect ) " & _
                  "SELECT Family, Infrafamily, BoxNo, BayNo, ShelfNo, BoxedAsCollection " & _
                  "FROM " & sTable & _
                  "GROUP BY Family, Infrafamily, BoxNo, BayNo, ShelfNo, BoxedAsCollection " & _
                  "HAVING (((BoxNo) > 0) And ((BayNo) = 0) And ((ShelfNo) = 0)) " & _
                  "ORDER BY Family, BoxNo, BoxedAsCollection"
            DoCmd.RunSQL str
            .MoveNext
        Loop While Not .EOF
    End With
End Sub

 

[June7]

Need a space after table name before GROUP BY

"FROM " & sTable & " " & _

 

[GPGeorge]

I have scanned the net and got so may different answers, that don't work, to this question'
I am trying to replace the "From" clause with a variable in the code below.
I keep getting an error in the from clause no matter what variations I try.

Private Sub Command100_Click()
    Dim sTable  As String
    Dim sCommon As String
    Dim rs      As Recordset
    Dim str     As String
    Set rs = oDB.OpenRecordset("TableList", dbOpenSnapshot)
    With rs
        .MoveFirst
        Do
            sTable = !TableName
'            sCommon = !CommonName
            str = "INSERT INTO tempfamily ( Family, Infra, BoxNo, BayNo, ShelfNo, Collect ) " & _
                  "SELECT Family, Infrafamily, BoxNo, BayNo, ShelfNo, BoxedAsCollection " & _
                  "FROM " & sTable & _
                  "GROUP BY Family, Infrafamily, BoxNo, BayNo, ShelfNo, BoxedAsCollection " & _
                  "HAVING (((BoxNo) > 0) And ((BayNo) = 0) And ((ShelfNo) = 0)) " & _
                  "ORDER BY Family, BoxNo, BoxedAsCollection"
            DoCmd.RunSQL str
            .MoveNext
        Loop While Not .EOF
    End With
End Sub

Two things.

One of the trouble-shooting steps that can help with dynamic SQL is to put

Debug.Print str

immediately after the line that creates it and before the line that runs it.
Then you can copy the SQL from the immediate window and put it in a query and usually that reveals the problem in the generated SQL.

The other thing is that, while DoCmd.RunSQL does execute action queries, it also raises warning message that the query is going to run, and error messages, if any. You can avoid the warning messages by using a different syntax that doesn't raise the warning messages, only the error messages.

CurrentDB.Execute str, dbFailOnError

I recommend it for that reason.

I think the string printed to the immediate window will reveal the syntax error.

 

[John Sh]

Need a space after table name before GROUP BY

"FROM " & sTable & " " & _

Thanks June7 but I now get a syntax error in the From clause.
I tried "From ' " & stable & "' " & _ with the same result.
If I put the space in front of "Group by", " Group by" I get "incomplete query clause"

 

[John Sh]

Two things.

One of the trouble-shooting steps that can help with dynamic SQL is to put

Debug.Print str

immediately after the line that creates it and before the line that runs it.
Then you can copy the SQL from the immediate window and put it in a query and usually that reveals the problem in the generated SQL.

The other thing is that, while DoCmd.RunSQL does execute action queries, it also raises warning message that the query is going to run, and error messages, if any. You can avoid the warning messages by using a different syntax that doesn't raise the warning messages, only the error messages.

CurrentDB.Execute str, dbFailOnError

I recommend it for that reason.

I think the string printed to the immediate window will reveal the syntax error.

Thanks GPGeorge.
I'll try that.
As or the warnings I usually wrap such things in a setwarnings pair.

 

[GPGeorge]

Thanks GPGeorge.
I'll try that.
As or the warnings I usually wrap such things in a setwarnings pair.

Not needed with the alternative syntax. No risk of turning warnings off and forgetting to turn them on again, or having an error crash the procedure with warnings turned off.

But do try the Debug.Print strategy to expose the actual syntax error.

 

[June7]

Don't add the apostrophes.

If table name or field names have spaces or punctuation, use [ ].

 

[John Sh]

Don't add the apostrophes.

If table name or field names have spaces or punctuation, use [ ].

Thanks again June7.
I just figured it out myself, with a bit of help from GPGeorge. "FROM [" & sTable & "]" & _
I completely forgot about the spaces in table names.
Thank you all for your assistance.
John

 

[cheekybuddha]

I dread to imagine your database schema design if you have multiple tables all with the same structure!!

What is variable sCommon used for (TableList.CommonName) ?

These should all be a single table with an extra field for whatever 'data' you contain in the table names (and CommonName)

 

[John Sh]

I dread to imagine your database schema design if you have multiple tables all with the same structure!!

What is variable sCommon used for (TableList.CommonName) ?

These should all be a single table with an extra field for whatever 'data' you contain in the table names (and CommonName)

!) I have inherited a database that, by it's very nature, holds various tables with "similar", not "the same", data structures. Yes, it is probably possible to incorporate some of this data into multiple related tables but the cost in time would far outweigh any advantage gained and, if fully normalised, I would end up with many more tables than I already have.
2) The table names and the form names are not always the same, so sCommon is the form name while sTable is the table name.
3)There are two kinds of things, ones that work and ones that don't. Mine works. It may not be Kosher, it is different. That doesn't equate to wrong.

 

[ebs17]

There are two kinds of things, ones that work and ones that don't. Mine works. It may not be Kosher, it is different. That doesn't equate to wrong.

No, there are three things.
- Things that don't work.
- Things that work.
- Things that work WELL.

"SELECT Family, Infrafamily, BoxNo, BayNo, ShelfNo, BoxedAsCollection " & _
                  "FROM " & sTable & _
                  "GROUP BY Family, Infrafamily, BoxNo, BayNo, ShelfNo, BoxedAsCollection " & _

INSERT INTO tempfamily

Such things raise fears of subsequent problems.