Problem with executing an SQL insert statement in access using VBA

Question

I'm trying this SQL statement but I keep getting an error

Run-time error '3061':
Too few parameters. Expected 4.

The statement I am trying to execute is:

sSQL = "INSERT INTO MasterTable (AdmissionNumber, Surname, Forename, TutorGroup) VALUES (intAdmissionNo, strSurName, strForeName, strTutorGroup)"
CurrentDb.Execute sSQL, dbFailOnError

The 4 values intAdmissionNo, strSurName, strForeName and strTutorGroup all have values and the fields all exist.

Any help is much appreciated, thanks! :)

Where have you provided the actual values that you want to insert??? — Sidharth Panwar
– Sidharth Panwar, Commented Nov 22, 2010 at 13:10
They're previously declared, they show up in the watch window as well. (but admission no will be a number that changes, the forname/surname are forname/surname and tutorgroup changes also). One example might be: ...VALUES (5040, Smith, John, 10R) — Tim
– Tim, Commented Nov 22, 2010 at 13:19

Hans Olsson · Accepted Answer · 2010-11-22 13:09:17Z

4

I haven't done that from VBA, but unless I'm mistaken you're not sending in the variables as you think you are. Try this line instead of your current one:

sSQL = "INSERT INTO MasterTable (AdmissionNumber, Surname, Forename, TutorGroup) VALUES (" & intAdmissionNo & ", " & strSurName & ", " & strForeName & ", " & strTutorGroup & ")"

answered Nov 22, 2010 at 13:09

Hans Olsson

55.1k16 gold badges99 silver badges118 bronze badges

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

4 Comments

Tim Over a year ago

Ahh thanks, but that now gives me a run time error 3075. Syntax Error (missing operator) in query expression '10R'. (10R is what TutorGroup has evaluated it)

smirkingman Over a year ago

tutorgroup is alphabetic, not numeric, add quotes ", '" & strTutorGroup & "')"

Tim Over a year ago

Ahh, thanks :) I put quotes around all the strings and it worked. Although now I seem to get an error every time someone's name has an apostrophe in it. Any ideas how to get round this without removing the apostrophes?

Hans Olsson Over a year ago

@Tim: Replace any instance of an apostrophe with two apostrophe's. Something like this Surname = Replace(Surname, "'", "''") I think.

Collectives™ on Stack Overflow

Problem with executing an SQL insert statement in access using VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related