SQL Embedded in VBA using Queryopen

[k j p]

I will retry posting since the other post is raising an error on the page.

I have a SELECT statement which uses OPENQUERY calling another SELECT statement...runs fine in a standard query tool:

SELECT OEE_PartNumber,
AVG(OEE_LineOverallOEE) as
OEE_AVG ,
SUM(OEE_GoodFootage) as FT_Good,
SUM(OEE_BadFootage) as OEE_FT_Bad
FROM
OPENQUERY(INSQL,
"SELECT DateTime, [OEE_LineID_String], [OEE_PartNumber],
[OEE_ProductFamily],
[OEE_GoodFootage], [OEE_BadFootage], [OEE_LineOverallOEE]
FROM WideHistory
WHERE
[OEE_ProductionShift] = 3
AND [OEE_PartNumber] =
'Part2'
AND
wwRetrievalMode = 'Cyclic'
AND wwVersion = 'Latest'
AND DateTime >=
'2014-03-01'")
group by OEE_PartNumber


However, when attempting to embed this query in VBA (tied to a command button in Excel for example), I usually build the SQL statement using a temporary string variable: For example:

Dim sqlstr as string

sqlstr = ""
sqlstr = sqlstr & "SELECT OEE_PartNumber, "
sqlstr = sqlstr & "AVG(OEE_LineOverallOEE) as OEE_AVG , "
sqlstr = sqlstr & "SUM(OEE_GoodFootage) as FT_Good, "
sqlstr = sqlstr & "SUM(OEE_BadFootage) as OEE_FT_Bad "
sqlstr = sqlstr & "FROM "
sqlstr = sqlstr & "OPENQUERY(INSQL, "SELECT DateTime, [OEE_LineID_String], [OEE_PartNumber], "
sqlstr = sqlstr & "[OEE_ProductFamily], [OEE_GoodFootage], [OEE_BadFootage], OEE_LineOverallOEE]"
sqlstr = sqlstr & "FROM WideHistory " sqlstr = sqlstr & " WHERE [OEE_ProductionShift] = 3 "
sqlstr = sqlstr & " AND [OEE_PartNumber] = 'Part2' "
sqlstr = sqlstr & " AND wwRetrievalMode = 'Cyclic' "
sqlstr = sqlstr & " AND wwVersion = 'Latest' "
sqlstr = sqlstr & " AND DateTime >= '2014-03-01'") "
sqlstr = sqlstr & "group by OEE_PartNumber "

This SQL string causes an error on execute due to the use of double quotes which start off the SELECT in the OPENQUERY section. Replacing with single, and double single quotes doesn't work. Any suggestions on how to overcome this?

[k j p]

Based on info from a Google search for substitute quotes SQL OPENQUERY, I seem to have found a way to get this to work, substituting the quotes within the OPENQUERY statement with " & Chr(34) & ".

[Bob Phillips]

Wouldn't this work?

Dim sqlstr As String

sqlstr = "SELECT OEE_PartNumber, "
sqlstr = sqlstr & "AVG(OEE_LineOverallOEE) as OEE_AVG , "
sqlstr = sqlstr & "SUM(OEE_GoodFootage) as FT_Good, "
sqlstr = sqlstr & "SUM(OEE_BadFootage) as OEE_FT_Bad "
sqlstr = sqlstr & "FROM "
sqlstr = sqlstr & "OPENQUERY(INSQL, ""SELECT DateTime, [OEE_LineID_String], [OEE_PartNumber], "
sqlstr = sqlstr & "[OEE_ProductFamily], [OEE_GoodFootage], [OEE_BadFootage], OEE_LineOverallOEE]"
sqlstr = sqlstr & "FROM WideHistory "
sqlstr = sqlstr & " WHERE [OEE_ProductionShift] = 3 "
sqlstr = sqlstr & " AND [OEE_PartNumber] = 'Part2' "
sqlstr = sqlstr & " AND wwRetrievalMode = 'Cyclic' "
sqlstr = sqlstr & " AND wwVersion = 'Latest' "
sqlstr = sqlstr & " AND DateTime >= '2014-03-01'"") "
sqlstr = sqlstr & "group by OEE_PartNumber "