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 q

Solution 1:

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 = 0Then
    cdb.Execute "INSERT INTO myTable (Field1, Field2) VALUES ('10', '11')", dbFailOnError
EndIfSet 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
IfRecordsAffected=0 Then
    conntemp.Execute "INSERT INTO myTable (Field1, Field2) VALUES ('10', '11')"
End If