将数据从 Excel 用户窗体复制到 Access 表时出现语法错误

Question

尝试通过 SQL 将数据从 Excel 用户表单复制到 Access 表时，我在数据库中的"是/否"类型的字段上遇到错误。如何正确设置信息的格式以更新数据库？

我查看了将数据复制到 Access 中的"是/否"字段中的其他一些示例，以及尝试使用多种不同格式的代码，带有双引号、单引号等

如下所示

Dim oConn As ADODB.Connection
Dim oCm As ADODB.Command
Dim iRecAffected As Integer
If Me.TBField.Visible = False Then
'Update office users
Init1 = Left(Me.TxtName1, 1) & Mid$(Me.TxtName1, InStr(Me.TxtName1, " ") + 1, 1)
    Select Case Me.LblFraOffice.Caption
        Case Is = "Add User"
            Set oConn = New ADODB.Connection
            oConn.Open sConn
            Set oCm = New ADODB.Command
            oCm.ActiveConnection = oConn
            If RbUser.Value = True Then
            oCm.CommandText = "INSERT Into Users (Name, Username, Initials, Process, FLM, FLM_ID, User, JobRole) " & _
            "VALUES ('" & TxtName1.Value & "','" & TxtUname1.Value & "','" & Init1 & "','" & TxtProcess1.Value & "','" & TxtFLM.Value & "','" & TxtFLMID.Value & "', True ,'" & TxtPosition1.Value & "')"
            ElseIf RbAdmin.Value = True Then
            oCm.CommandText = "INSERT Into Users (Name, Username, Initials, Process, FLM, FLM_ID, JobRole, User) " & _
            "VALUES ('" & TxtName1.Value & "','" & TxtUname1.Value & "','" & Init1 & "','" & TxtProcess1.Value & "','" & TxtFLM.Value & "','" & TxtFLMID.Value & "', True ,'" & TxtPosition1.Value & "')"
            ElseIf RbManager.Value = True Then
            oCm.CommandText = "INSERT Into Users (Name, Username, Initials, Process, FLM, FLM_ID, JobRole, User) " & _
            "VALUES ('" & TxtName1.Value & "','" & TxtUname1.Value & "','" & Init1 & "','" & TxtProcess1.Value & "','" & TxtFLM.Value & "','" & TxtFLMID.Value & "', True ,'" & TxtPosition1.Value & "')"
            End If
            'MsgBox oCm.CommandText
            oCm.Execute iRecAffected
            oConn.Close

在用户表单上按提交时，这应该将新创建的用户添加到数据库表中。如果我删除用户字段(以及关联的真/假(，它可以工作。但我的用户可以是用户、管理员或管理员

目前我收到一条错误消息

运行时错误"-2147217900 (80040e14("：插入 INTO 语句中的语法错误

Answer 1

列名User是保留关键字。您可以在关键字周围添加方括号来解决问题吗：

INSERT Into Users (Name, Username, Initials, Process, FLM, FLM_ID, [User], JobRole)

或者，可以将列名称重命名为非保留关键字以消除此错误。