CODEWITHSUNDEEP
sqlms-accessinsert-updateupsert
Jim
Jim

Reputation: 381

Error in my SQL statement (UPSERT)

I'm trying to do an UPSERT (since that's what it appears to be called) and I'm getting an error: Microsoft JET Database Engine error '80040e14' Syntax error (missing operator) in query expression ...

UPDATE myTable
SET Field1='10', Field2='11'
WHERE Date = #06/05/2013#
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO myTable
(Field1, Field2)
VALUES (10, 11)
END

Upvotes: 1

Views: 997

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

Your code is using T-SQL (SQL Server) syntax that Access SQL does not understand. The VBA equivalent would be:

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "UPDATE myTable SET Field1='10', Field2='11' WHERE [Date] = #2013-06-05#", dbFailOnError
If cdb.RecordsAffected = 0 Then
    cdb.Execute "INSERT INTO myTable (Field1, Field2) VALUES ('10', '11')", dbFailOnError
End If
Set cdb = Nothing

Notes:

  1. Date is a reserved word in Access, so you should specify the field name as [Date].

  2. Notice that the code uses the unambiguous date format yyyy-mm-dd. You should use that format any time you have a date literal enclosed in hash marks (#).

  3. Note also that it fixes the type mismatch in your code: The UPDATE statement tried to update the fields as text, while the INSERT statement tried to insert them as numbers.

Edit re: comment

The ADO equivalent of the above would be

conntemp.Execute "UPDATE myTable SET Field1='10', Field2='11' WHERE [Date] = #2013-06-05#", RecordsAffected
If RecordsAffected = 0 Then
    conntemp.Execute "INSERT INTO myTable (Field1, Field2) VALUES ('10', '11')"
End If

Upvotes: 4

Related Questions