CODEWITHSUNDEEP
sqlvbaparameters
adam Kearsley
adam Kearsley

Reputation: 999

Access VBA - Pass Param to SQL query?

I have created a function in VBA to connect to a web service and retrieve an XML file. I then wanted to parse the XML and import the captured nodes into the SQL database.

I have managed to connect to the web service and parse the XML file and even show the values in a messagebox, but when i try and store the parameter via a SQL query, it asks me for a parameter value?

Example:

Set BodyStyle = domResponse.SelectSingleNode("/GetVehicles/DataArea/Vehicles/Vehicle/BodyStyle")


MsgBox (BodyStyle.Text)

DoCmd.RunSQL "INSERT INTO vHPI (BodyStyle) BodyStyle.Text"

The messagebox pops up with a value of MOTORCYCLE, but then i get a prompt asking me for a parameter for BodyStyle.Text

I dont understand how the system can show the parameter in a messagebox but say the parameter is empty when i want to insert it into the database?

Please help! Thanks Adam.

Upvotes: 0

Views: 327

Answers (2)

kay10
kay10

Reputation: 94

You can simply do this:

DoCmd.RunSQL "INSERT INTO vHPI (BodyStyle) VALUES ('" & BodyStyle.Text & "')"

Assuming your column is named BodyStyle and the table is vHPI

Upvotes: 2

Bafsky
Bafsky

Reputation: 771

Right now you have the literal value of "BodyStyle.Text" on your SQL statement. Try it like this...

DoCmd.RunSQL "INSERT INTO vHPI (BodyStyle) " + BodyStyle.Text

Upvotes: 0

Related Questions