Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access 2010 VBA Execute SQL Too Few Parameters

Posted on 2014-09-12
4
Medium Priority
?
384 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 58

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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