Trying to execute a SQL statement from VBA to an Access database, getting a syntax error with the INSERT INTO statement

Question

This is the code:

sqlStr = "INSERT INTO Students (ID, Last Name, First Name, E-mail Address, Student ID, Level, Date of Birth, Home Phone, Address, City, State/Province) VALUES ('" & ID.Value & "','" & lastName.Value & "','" & firstName.Value & "','" & email.Value & "','" & studentID.Value & "','" & birthday.Value & "','" & phone.Value & "','" & address.Value & "','" & city.Value & "','" & state.Value & "');"

DoCmd.RunSQL sqlStr

The .Value variables are coming from text boxes from a userform.

What is the error? You insert all values as text which hopefully isn't correct. — Gustav, Nov 28 '19 at 07:43

score 1 · Answer 1 · answered Nov 28 '19 at 01:10

You could open the table and insert the record directly without building the sql statement.

Dim rs as dao.recordset 
set rs = Currentdb.tabledefs("Students").openrecordset
Rs.addnew
    ' both rs(fieldname) and rs!fieldName would work
    Rs("ID") =  id 
    Rs![Last Name] = lastName
    rs![First Name] =
    rs![E-mail Address]=
    rs![Student ID]=
    rs!Level=
    rs![Date of Birth]=
    rs![Home Phone]=
    rs!Address=
    rs!City=
    rs![State/Province]=

Rs.update
Rs.close
Set rs = nothing

Assign rest of the text box values for each field and try it. At least you can escape from syntax error

replying from mobile

... and from SQL injection and data formatting issues! – Andre Nov 28 '19 at 08:25 — Andre, Nov 28 '19 at 08:25

score 0 · Answer 2 · answered Nov 27 '19 at 23:42

0

Try this:

sqlStr = "INSERT INTO Students (ID, [Last Name],  [First Name],  [E-mail Address], [Student ID], Level,  [Date of Birth],  [Home Phone], Address, City,  [State/Province]) VALUES ('" & ID.Value & "','" & lastName.Value & "','" & firstName.Value & "','" & email.Value & "','" & studentID.Value & "','" & birthday.Value & "','" & phone.Value & "','" & address.Value & "','" & city.Value & "','" & state.Value & "');"

answered Nov 27 '19 at 23:42

zip

3,860
2
8
18

Tried it, still getting the same error – skirsch00 Nov 28 '19 at 00:20

score 0 · Answer 3 · answered Nov 27 '19 at 23:42

You'll need square brackets surrounding fields with spaces or special characters, e.g.:

(ID, [Last Name], [First Name], [E-mail Address], [Student ID], Level, [Date of Birth], [Home Phone], Address, City, [State/Province])

Also, unless all of your fields are text fields, you'll need to correctly represent the values depending on the data type - for example, if Date of Birth is a date field (as it should be), the date value should be supplied in the format:

#mm/dd/yyyy#

score 0 · Answer 4 · answered Nov 28 '19 at 00:02

The names of the fields with spaces need to be closed between sqaure brackets and check the data type of your values. Additionally you need to use a single quote (') in your varchar .Values.

sql_qry = "INSERT INTO STUDENTS(ID, [NAME ST], AGE) VALUES(" & v1 & ",'" & v2 & "'," & v3 & ")"

then

DoCmd.RunSQL (sql_qry)

score 0 · Answer 5 · answered Nov 28 '19 at 01:13

You set 11 fields to insert, but give only 10 values. You lack the value of Level

sqlStr = "INSERT INTO Students "
sqlStr = sqlStr & "([ID], [Last Name], [First Name], [E-mail Address], [Student ID], [Level], [Date of Birth], [Home Phone], [Address], [City], [State/Province]) VALUES ("
sqlStr = sqlStr & "'" & ID.Value & "'"
sqlStr = sqlStr & ",'" & lastName.Value & "'"
sqlStr = sqlStr & ",'" & firstName.Value & "'"
sqlStr = sqlStr & ",'" & email.Value & "'"
sqlStr = sqlStr & ",'" & studentID.Value & "'"
sqlStr = sqlStr & "," & Level.Value & ""      '<--lack. if number
sqlStr = sqlStr & ",#" & birthday.Value & "#"   '<--if date, blocked with #
sqlStr = sqlStr & ",'" & phone.Value & "'"
sqlStr = sqlStr & ",'" & address.Value & "'"
sqlStr = sqlStr & ",'" & city.Value & "'"
sqlStr = sqlStr & ",'" & state.Value & "'"
sqlStr = sqlStr & ");"

Trying to execute a SQL statement from VBA to an Access database, getting a syntax error with the INSERT INTO statement

5 Answers5

Linked