insert if not already exist

Question

I created a function that performs an INSERT INTO sql statement, but I need to only do this if no entry exists.  Do I need to check before performing the Insert or can this become part of the Insert Statement or is this something that should be part of my table design?

I have ClientId, TypeId, DueDate (and a few more), but those cannot be duplicated within a ClientId.

QB

Answer 1

Just as another suggestion, you could create a Join to the table you're inserting into, using the "unmatched" query wizard. It might be something like

INSERT INTO targettable

SELECT A.ClientID, A.TypeID, A.DueDate, <other fields>

FROM sourcetable AS A LEFT JOIN targettable AS B

ON A.ClientID = B.ClientID

AND A.TypeID = B.TypeID

AND A.DueDate = B.DueDate

WHERE B.ClientID IS NULL;

If there is already a record in the target table which matches on ClientID, TYpeID and DueDate this query will return no records.

Answer 2

Could you explain how-to define a unique constrain in access?

Answer 3

Define a UNIQUE constraint at table level, so no dup would be allowed. The constraint can be on a single field, or can span many fields trough the index definition. The UNIQUEness will then be secured, whatever interface or program (in any language, VBA, C#, or application, Excel, ...) try to append a record (or try to modify an existing record).

Answer 4

It kind of depends on your insert statement.  What does it look like?

Are you using a form to gather the information and execute the query using a button?

Perhaps you need

  IF DCount("*","NameOfTable","ClientID=" & Me.ClientID & " AND TypeID =" & me.TypeID & " AND DueDate = #" & Me.DueDate & "#") =0 THEN

    'Execute the query

End IF