[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

MS Access 2010 VBA Execute SQL Too Few Parameters

Posted on 2014-09-12
4
Medium Priority
?
387 Views
Last Modified: 2014-09-12
I am using vba in an attempt to append a row in a table.

'Build the -Insert SQL Statement
strInsertSQL = "INSERT INTO tblFeature(Cat_No, Fea_ID, Fea_Name,Fea_Descr,Fea_Syntax,Fea_URL) " & _
"VALUES(intCatNo, strFeaID, strFeaName,strFeaDescr,strFeaSyntax,strFeaURL)"
 
'Execute the sql statement
CurrentDb.Execute strInsertSQL

Worked when values were hard coded.

Watch window shows values for all variables.

What am I missing?

Thanks,
0
Comment
Question by:Dovberman
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
Anthony Berenguel earned 1000 total points
ID: 40319683
try this
strInsertSQL = "INSERT INTO tblFeature(Cat_No, Fea_ID, Fea_Name,Fea_Descr,Fea_Syntax,Fea_URL) " & _
"VALUES(" & intCatNo & ", '" & strFeaID & "', '" & strFeaName & "', '" & strFeaDescr & "', '" & strFeaSyntax & "', '" & strFeaURL  & "')"

Open in new window

0
 
LVL 59

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 40319698
To add just a bit, you were passing the variable names as literal's in the statement, not the value those variables contain.

The way your statement was, the letters 'intCatNo' was actually being used for the value.   You can see that clearly by putting a STOP just above the execute, then looking at the SQL Statement.

Contrast that to what Anthony suggested and you'll see the difference.

Jim.
0
 

Author Comment

by:Dovberman
ID: 40319703
Yes, I noticed this when I hard coded the values.

Thanks,
0
 

Author Closing Comment

by:Dovberman
ID: 40319705
Thank you both.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question