Syntax error when copying data from Excel userform to Access table

Question

When trying to copy data from an Excel userform in to an Access table via SQL I am encountering an error on a field that is a Yes/No type in the database. How can I format the info correctly so that it updates the database?

I have looked at some other examples of copying data into the Yes/No fields in Access, as well as trying my code in a number of different formats, with double quotations, single quotations & as it appears below

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

When pressing submit on the userform this should add a newly created user into the database table. If I remove the User field (and the associated true/false) it works. But my users can be users, admins or managers

At present I receive an error message

Run-time error '-2147217900 (80040e14)': Syntax error in INSERT INTO statement

Arulkumar · Accepted Answer · 2019-06-03 09:52:48Z

2

Column name User is the reserved keyword. Can you add square brackets around the keyword to fix the issue:

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

or you can rename the column name to a non-reserved keyword to sovle this error.

answered Jun 3, 2019 at 9:52

Arulkumar

13.3k15 gold badges51 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Syntax error when copying data from Excel userform to Access table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related