Updating Access Database using UPDATE SQL statement in VBA

Question

Can someone take a look a the stSQL string and help me fix the syntax error I am getting associated with the UPDATE statement?

Run-time error '-2147217900 (8004e14)': Syntax error in UPDATE statement.

I have a rudimentary understanding of SQL and don't seem to understand where I have gone wrong.

I want to update the fields of Table 1 if the FileName UserForm value matches a FileName field in the Access Db.

Thanks

Public Sub UpdateDatabaseEntry()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stDB As String, stSQL As String, stProvider As String
Dim FileName As String
Dim Nickname As String
Dim RecipientName As String
Dim RecipientRelationship As String
Dim Summary As String
Dim Noteworthy As String
Dim PreparedBy As String

FileName = UserForm1.FileNameTextBox.Text
Nickname = UserForm1.NicknameTextBox.Text
RecipientName = UserForm1.RecipientNameTextBox.Text
RecipientRelationship = UserForm1.RecipientRelationshipComboBox.Text
Summary = UserForm1.SummaryTextBox.Text
Noteworthy = UserForm1.NoteworthyCheckBox.Value
PreparedBy = UserForm1.PreparedByTextBox.Text

stDB = "Data Source= E:\MyDb.accdb"
stProvider = "Microsoft.ACE.OLEDB.12.0"

//Opening connection to database
With cn
    .ConnectionString = stDB
    .Provider = stProvider
    .Open
End With

//SQL Statement telling database what to do
stSQL = "UPDATE Table1" & _
        "SET Nickname= '" & Nickname & "', RecipientName= '" & RecipientName & "', " & _
            "RecipientRelationship= '" & RecipientRelationship & "', Summary= '" & Summary & "', " & _
            "Noteworthy= '" & Noteworthy & "', PreparedBy= '" & PreparedBy & "', " & _
        "WHERE FileName= '" & FileName & "'"

cn.Execute stSQL

cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

In general, when you have a problem like this, just do a Debug.Print stSQL right before you execute the SQL. That way you see in the Immediate window exactly what the SQL is, and that helps you find any syntax errors. — Yawar
– Yawar, Commented Feb 16, 2014 at 21:53

Gordon Linoff · Accepted Answer · 2014-02-16 16:38:59Z

2

At least one problem is caused by lack of spaces in the query. So your query started UPDATE Table1set.

stSQL = "UPDATE Table1 " & _
        "SET Nickname= '" & Nickname & "', RecipientName= '" & RecipientName & "', " & _
            "RecipientRelationship= '" & RecipientRelationship & "', Summary= '" & Summary & "', " & _
            "Noteworthy= '" & Noteworthy & "', PreparedBy= '" & PreparedBy & "'" & _
        "WHERE FileName= '" & FileName & "'"

If this doesn't fix the problem. Then edit your question with the value of stSQL after the variable substitution.

EDIT:

As TS points out, another problem is the , before the where (fixed above).

edited Feb 16, 2014 at 16:38

answered Feb 16, 2014 at 16:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Gordon Linoff Over a year ago

@T.S. . . . Yes, that would count as another problem.

blahblahblah Over a year ago

Thanks! The comma after the , before the WHERE was really my issue. Everything works!

Gordon Linoff Over a year ago

@T.S. . . . You should put the comment into an answer so the OP can accept it.

Collectives™ on Stack Overflow

Updating Access Database using UPDATE SQL statement in VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related