How to use SQL from a Query in VBA code

[mattc66 via AccessMonster.com]

I want to take the SQL from a query and paste it in VBA to work behind a
DoCmd.RunSQL what changes to the SQL Code do I need to make to use it?

This is the Update Query that I want to use:

UPDATE tblQuoteCalc INNER JOIN tblOrderDetails ON tblQuoteCalc.OrderID =
tblOrderDetails.OrderID SET tblOrderDetails.Description = tblQuoteCalc.
Description, tblOrderDetails.WoodType = tblQuoteCalc.WoodType,
tblOrderDetails.W = [tblQuoteCalc].[Width], tblOrderDetails.L = [tblQuoteCalc]
..[Length], tblOrderDetails.H = [tblQuoteCalc].[Height], tblOrderDetails.Cost
= tblQuoteCalc.Cost, tblOrderDetails.ListPrice = tblQuoteCalc.ListPrice
WHERE (((tblOrderDetails.Item)="ROOM"));

Why not just execute the Query in code? Because when I do it says the query
is empty. It's a timing issue from when the data is available in the
QuoteCalc to then be copies over to the OrderDetail table.

Thanks
Matt

 

[Douglas J. Steele]

I don't see why running the SQL, rather than the query, will make any
difference to timing errors.

Having said that, though, you wouldn't make any changes whatsoever to the
SQL. Note, though, that because you're using double quotes in the SQL, you
have to double them up to ensure that the resultant string variable contains
the double quotes:

strSQL = "UPDATE ... WHERE (((tblOrderDetails.Item)=""ROOM""));"

Alternatively, you can use single quotes as the text delimiter:

strSQL = "UPDATE ... WHERE (((tblOrderDetails.Item)='ROOM'));"

BTW, I'd recommend using the DAO Execute method, rather than RunSQL for two
reasons.

1) RunSQL is going to pop up a message box "You're about to update .....".
Execute doesn't.
2) If you use the following syntax, the Execute method will generate a
trappable error if something goes wrong running the query:

CurrentDb.Execute strSQL, dbFailOnError