Query in code

[Crystal]

I have an append query that runs fine when done as a saved
query. It appends records to a SQL Server table. When I
transfer the SQL to code and try to execute it using
db.Execute strSQL, it gives me an "ODBC call fail" error.
The string is identical to the SQL in the saved query.
What causes this?

Any suggestions?

Crystal

 

[Alex Dybenko]

this SQL Server table - is it linked?
try the following - make a new query, paste execute there and run it - will
it give you error?
else - paste sql here

 

[Crystal]

The query runs fine as a saved query. The SQL is below:

INSERT INTO dbo_3MSA_tblScheduleMonitor ( StockNo,
Product_Name, PL_3M, Status, FamCode )
SELECT qryFlyFamily.StockNo, qryFlyFamily.[Product Name],
IIf([Product Name] Like "3M*","3M","PL") AS PL, "H" AS
Stat, qryFlyFamily.FamCode
FROM qryFlyFamily LEFT JOIN dbo_3MSA_tblScheduleMonitor ON
qryFlyFamily.StockNo = dbo_3MSA_tblScheduleMonitor.StockNo
WHERE (((qryFlyFamily.FamCode)="crystalsfamilycode") AND
((dbo_3MSA_tblScheduleMonitor.StockNo) Is Null));

This is the VBA code:

Dim str3M, strLike3M, strPL, strStatus, strSubSQL As String
str3M = "3M"
strLike3M = "3M*"
strPL = "PL"
strStatus = "H"

strSubSQL = "IIF([Product Name] Like """ & strLike3M
& """" & ",""" & str3M & """" & ",""" & _
strPL & """" & ") AS Type"

'Add the line(s) to dbo_3MSA_tblScheduleMonitor
strSQL = "INSERT INTO dbo_3MSA_tblScheduleMonitor" & _
" ( StockNo, Product_Name, PL_3M, Status, FamCode )" & _
" SELECT qryFlyFamily.StockNo," & _
" qryFlyFamily.[Product Name], " & strSubSQL & ",""" &
strStatus & """" & _
" AS Stat, qryFlyFamily.FamCode" & _
" FROM qryFlyFamily LEFT JOIN dbo_3MSA_tblScheduleMonitor"
& _
" ON qryFlyFamily.StockNo =
dbo_3MSA_tblScheduleMonitor.StockNo" & _
" WHERE (((qryFlyFamily.FamCode)=""" & strFamCode & """"
& ") AND" & _
" ((dbo_3MSA_tblScheduleMonitor.StockNo) Is Null));"

db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected

Crystal