I am building a SQL string in VBA in order to update selected records. The SQL string, sSql, is the following:
UPDATE (NTbl_Boom_Detail AS D INNER JOIN NTbl_Boom_Detail_D1 AS D1 ON D.AssortmentID = D1.AssortmentID) INNER JOIN NTbl_Colors AS C ON D.Color = C.Color SET D.Climate = "ALL" WHERE D.ArticleNum="570077459" AND C.ColorCode="BLK 000001";
When I try to use the Cnxn.Execute sSql,dbFailOnError command, I get an Incorrect Syntax near "(" error message from SQL Server.
If I copy and paste the sSql string into the SQL view of an Access query, it runs fine.
Can someone tell my why Access takes the SQL string, but SQL Server doesn't?
Thanks, Eddie