尝试执行从VBA到Access数据库的SQL语句，并使用INSERT INTO语句获取语法错误 [英] Trying to execute a SQL statement from VBA to an Access database, getting a syntax error with the INSERT INTO statement

问题描述

这是代码:

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

.Value变量来自用户表单的文本框.

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

推荐答案

您可以打开表并直接插入记录，而无需构建sql语句.

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

为每个字段分配其余的文本框值，然后尝试一下.至少您可以从syntax error

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

通过手机回复

这篇关于尝试执行从VBA到Access数据库的SQL语句，并使用INSERT INTO语句获取语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！