insert or update

[Guest]

Hello,

i use an Access database for my Excel application i'm exporting some data
into it. I use ADO functions to insert or update data.
But i would like to know how to tell my function to insert if the primary
key doesn't exist and to update data if it exists.

Many thanks !

 

[Guest]

hi,
trying to update and append and the same time is dangerous
and is not recommended. they should be run as seperate
operations.

 

[Jamie Collins]

Use the Connection object's Execute method, whose second parameter is
RecordsAffected. Do the UPDATE first and test the RecordsAffected; it
should be a simple matter to determine whether it was successful. If
the UPDATE failed, try the INSERT INTO next.

Alternatively, you could fetch a disconnected recordset and Filter it
using the PK column value and use RecordCount to determine whether it
exists (in a multi-user environment you may have to reconnect and
requery to ensure you have an up-to-date data). You could even make the
changes to the recordset, reconnect and let the recordset do the
UPDATEs and INSERTs by issuing an UpdateBatch.

trying to update and append and the same time is dangerous
and is not recommended.

Is it even possible? A recordset seemingly does this but really, under
the hood, it is executing either UPDATE or INSERT INTO as appropriate.
Jamie.

--