If exist update, else insert

Question

I want to insert record if corresponding record and update if existing record.

To check if corresponding record exists, which way is faster?

Should I use SELECT query? 

Can I use IF EXIST like following?

    IF EXIST(SELECT * FROM.....)

         UPDATE .....

    ELSE

         INSERT ......

Answer 1

I want to insert record if corresponding record and update if existing record.

Hi Jeff,

You could use something like:

Sub Test()
  Dim cur_set As Recordset     'DAO.Recordset
   
  Set cur_set = CurrentDb.OpenRecordset("SELECT * FROM <table> WHERE <field> = <value>")
  
  With cur_set
    If .EOF Then
      .AddNew
    Else
      .Edit
    End If
    .<field1> = <value1>
    .<field2> = <value2>
    ...
    .Update
  End With
End Sub

Change all items between <> with your own data.

Imb.

Answer 2

I create a select query that will result in 1 record if the record exists (Q1)

then I use:

if DCount("*","Q1") =1 then

.....it provides the decision as to whether to fire an append query or an update query

what I like is that as a stand alone select query - one can manually test Q1 easily to debug

Answer 3

Hi Jeff,

Would something like this work?

Update Query with Append

Hope it helps...

Answer 4

Hi Jeff,

Do you have any issue with this thead?

If not, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

If you do, please feel free to let us know.

Best Regards,

Tao Zhou

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

It can be done directly in a query without the need of any code.  See Doug Steele's article on the subject at http://www.vb123.com.au/200311_ds_aa.htm (he also has a whole chapter on the subject in his book https://www.wiley.com/en-ca/Access+Solutions%3A+Tips%2C+Tricks%2C+and+Secrets+from+Microsoft+Access+MVPs-p-9780470591680 (Part II, Tip 4).

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net