INSERT INTO If Not Exists

[Only4Access]

Hi Experts,

I have a form bounded to a temporary table (only for bulk data entry). When click on a button, I am trying to INSERT records If those are Not Exists in a table.



This is what I tried.

Dim MyDate As Date
Dim MyNo As Long
Dim MyRec As Long
Dim strSQL As String
 
MyDate = Dlookup(“DateID”,”tblTemp”)
MyNo = Dlookup(“NoID”,”tblTemp”)
MyRec = Dlookup(“RecID”,”tblTemp”)
 
strSQL = "INSERT INTO tblAct (DateID, NoID, RecID)" & _
         "VALUES (#" & MyDate & "#, " & MyNo & ", " & MyRec & ")" & _
         "ON DUPLICATE KEY UPDATE [DateID] = [DateID];"
 
If Me.Dirty Then
        Me.Dirty = False
End If
             
CurrentDb.Execute strSQL, dbFailOnError

But this is not working. Giving error 'Run Time Error 3137' "Missing semicolon (; ) at end of SQL statement.

MyDate, MyNo & MyRec are getting the first value from a field.

Thank you for your time.

[pbaldy]

To the best of my knowledge that isn't valid SQL. Try

strSQL = "INSERT INTO tblAct (DateID, NoID, RecID)" & _
         "VALUES (#" & MyDate & "#, " & MyNo & ", " & MyRec & ")"

That may error inserting a duplicate primary key, and this may be a case when dropping dbFailOnError is appropriate.

[Only4Access]

Thank you Paul, That works. Thanks a lot.

But, how can i INSERT only IF NOT EXISTS in tblAct? tblAct has Auto_Number PK & this PK have a relation in another table. Below this particular code I have an Append query DoCmd.OpenQuery.....

So If this exists stop & continue below Append query. How can i do that?

Thank you for your time Paul.

[pbaldy]

You could use DCount() to test before continuing.

[Only4Access]

Sorry for the delay in reply Paul, due to my sickness. Thank you very much. Dcount did the trick. Still learning VBA....

One last question.

I have to check 4 conditions in Dcount, since my table is increasing day by day, does it cause any performance issue? Is there any alternate faster way, such as SQL NOT EXISTS?