Update or Append (Insert) a Single Record

What if you want to update a record, but if its not already in the table you want to insert it (Append query in Access).

One way is to check if the record is there with a select statement and then either update or append.

But easier is to run the update statement and then check the recordsAffected property of the database object to see if the value is equal to 0, if not run the append (Insert) query.

For example:

sqlUpdateStatement = "Update field1 where field 2 = somevalue"

dbs.execute sqlUpdateStatement , dbFailOnError

if dbs.RecordsAffected = 0 then
dbs.Execute sqlAppendStatement
end if

What about multiple records?

Well, then you'd have to know the count of the records you want updated (a select statement run priorly), then run the update, check if records affected was less, if so append where id not in (records already in the table).