Access VBA - Pass Param to SQL query?

Question

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.

kay10 · Accepted Answer · 2013-04-15 14:40:42Z

2

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

edited Apr 15, 2013 at 14:40

answered Apr 15, 2013 at 13:54

kay10

941 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bafsky · Accepted Answer · 2013-04-15 13:53:36Z

0

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

answered Apr 15, 2013 at 13:53

Bafsky

7811 gold badge7 silver badges16 bronze badges

Collectives™ on Stack Overflow

Access VBA - Pass Param to SQL query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related