If exist update, else insert RRS feed

  • 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 .....


             INSERT ......

    Friday, May 18, 2018 11:10 PM

All replies

  • 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
        End If
        .<field1> = <value1>
    .<field2> = <value2> ... .Update End With End Sub

    Change all items between <> with your own data.


    Saturday, May 19, 2018 7:43 AM
  • 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

    Saturday, May 19, 2018 1:43 PM
  • Hi Jeff,

    Would something like this work?

    Update Query with Append

    Hope it helps...

    Saturday, May 19, 2018 3:31 PM
  • 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 MSDNFSF@microsoft.com.

    Wednesday, May 30, 2018 2:10 PM
  • 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

    Wednesday, May 30, 2018 2:44 PM