Can't do INSERT INTO...ON DUPLICATE KEY UPDATE in vb Access

Question

I want to make a request which will insert data or update if the entry exists.

I tried:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM dbo_VUE_GATE_ELEVES WHERE Annee = " & intAnneeScolaire)

While Not rs.EOF
    DoCmd.RunSQL ("INSERT INTO Eleve(Fiche, Nom, Prenom, Courriel) VALUES (rs!Fiche, '" & rs!Nom & "', '" & rs!Prenom & "', '" & rs!Courriel & "') ON DUPLICATE KEY UPDATE Nom = '" & rs!Nom & "', Prenom = '" & rs!Prenom & "', Courriel = '" & rs!Courriel & "'")
    rs.MoveNext
Wend

I always get the following error:

"Error 3137 - Missing semicolon(;) at end of SQL statement."

And yes I tried adding a semicolon. Thanks for your help!

Answer 1

Since Access does support a single statement to handle upsert needs like ON DUPLICATE KEY or MERGE, consider two action queries without any quotation or concatenation or recordset looping. Below separates SQL from VBA and uses parameterization for the insert-select query.

SQL (save both as saved queries in Access .accdb file)

PARAMETERS prmintAnneeScolaire INTEGER;
INSERT INTO Eleve (Fiche, Nom, Prenom, Courriel)
SELECT d.Fiche, d.Nom, d.Prenom, d.Courriel
FROM dbo_VUE_GATE_ELEVES d
WHERE d.Fiche NOT IN (SELECT Fiche FROM Eleve)
  AND d.Annee = [prmintAnneeScolaire]

UPDATE Eleve e INNER JOIN dbo_VUE_GATE_ELEVES d ON e.Fiche = d.Fiche
SET e.Nom = d.Nom,
    e.Prenom = d.Prenom,
    e.Courriel = d.Courriel

VBA

' APPEND NEW DATA
With CurrentDb.QueryDefs("mySavedAppendQuery")
    .Parameters("prmintAnneeScolaire") = intAnneeScolaire
    .Execute dbFailOnError
End With

' UPDATE EXISTING DATA
CurrentDb.Execute "mySavedUpdateQuery"