Passing values from VBA variable to SQL

[Nexus]

Hi,

How do I pass values from a VBA variable into a SQL
variable and vice versa? Below are the codes that I've
used and have encountered the following error:

"Run-time error '170'
Line 1: Incorrect syntax near 'tblVendors'"

Codes:

Dim strVendorID As String

DoCmd.RunSQL "DECLARE @strVendorID2 nvarchar (6)" _
& "SET @strVendorID2 = strVendorID" _
& "UPDATE tblVendors SET VendorID = '9898' WHERE VendorID
= @strVendorID2"

My take is that sql doesn't recognise strVendorID in line
2 of my codes as strVendorID belongs to VBA. Is this
really the problem? When i replace strVendorID with a
sample number, 9999(see codes below), the statement is
able to run.

Dim strVendorID As String

DoCmd.RunSQL "DECLARE @strVendorID2 nvarchar (6)" _
& "SET @strVendorID2 = '9999'" _
& "UPDATE tblVendors SET VendorID = '9898' WHERE VendorID
= @strVendorID2"

 

[Mary Chipman]

That's because you're passing your variable names as literals. SQL
Server is looking for a VendorID equal to "@strVendorID2" in the WHERE
clause, not the actual value in the variable.

--Mary

 

[Norman Yuan]

DoCmd.RunSQL "DECLARE @strVendorID2 nvarchar (6)" _
& "SET @strVendorID2 = " & strVendorID & _
& "UPDATE tblVendors SET VendorID = '9898' WHERE VendorID
= @strVendorID2"

 

[Nexua]

Yup, it does work! Thanks!

-----Original Message-----
DoCmd.RunSQL "DECLARE @strVendorID2 nvarchar (6)" _
& "SET @strVendorID2 = " & strVendorID & _
& "UPDATE tblVendors SET VendorID = '9898' WHERE VendorID
= @strVendorID2"




.