recordset AddNew primary key issue

hello,

I am wirting a function in VBA that adds a new record to an existing
database. If i try to add a record with a primary key that already
exists in the database, i get an error. I was wondering if there was
some way I could make AddNew overwrite any old entry with the same
primary key...I was hoping I wouldn't have to iterate through the
entire database each time I add an entry.

thanks

re: recordset AddNew primary key issue

sud...@gmail.com wrote:use DCount to see if it exists. if it does, use an update query,
otherwise, insert.

re: recordset AddNew primary key issue

pietlinden@hotmail.com wrote:wouldn't that be the equiavlent of potentially iterating through the
database twice? (once for DCount, and once to set the cursor for update
query). I was hoping there might be a more efficient way to update
using access...

re: recordset AddNew primary key issue

use DCount to see if it exists. if it does, use an update query,No, not really. If you use DCount, you're just checking for the
existence of the primary key value. If it exists, you have two
options: update the existing record or overwrite it. If you don't want
it, just pass the PK to a delete query, run the delete, then run the
insert.

sub ReplaceOldRecord(byval strPK as string)
dim cQUOTE as String = "'" '<--- single quote
currentDB.Execute("DELETE FROM myTable WHERE PrimaryKey=" &
cQUOTE & strPK & cQUOTE)
'put your insert here...
currentDB.Execute("INSERT INTO myTable(field1, field2...) VALUES
(me.cbo..., me.txt....)
end sub

re: recordset AddNew primary key issue

* sudarp@gmail.com:Definitely not. There would be no iterating through the database.
That's one of the advantages of indexes. DCount on a primary key is
extremely fast and efficient.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

re: recordset AddNew primary key issue

Randy Harris wrote:ok, thanks!