Check For Duplicate Entries Before Adding Data To Access Database

Using vbscript i am trying to add data to access database. I am successfully able to add data to database but i want to check duplicity before adding data to database. Duplicity s

Solution 1:

Why don't you just try adding Unique Indexes to Name, Phone and Id ? This way the database engine will discard those records automagically.
Possible drawback: you will not know which record has been dropped.

Solution 2:

My solution is quite elaborate: use an INSERT INTO ... SELECT query from a dummy table containing one record, then use a NOT EXISTS in there.

I'm going to use parameters too, you really should've done that yourself to avoid problems with SQL injection.

CurrentDb in this scenario is a DAO database object.

I'm going to only use 3 fields to keep the solution concise.

With CurrentDb.CreateQueryDef("", "INSERT INTO dvd (timestammp,Name,Phone) " & _ 
    " SELECT p1, p2, p3" & _
    " FROM (SELECT First(ID) FROM MSysObjects) As Dummy" & _
    " WHERE NOT EXISTS(SELECT 1 FROM dvd WHERE Name = p1)"
    .Parameters("p1") = txtteNow.value
    .Parameters("p2") = txtName.value
    .Parameters("p3") = txtPhone.value
    .Execute
End With