By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 996 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

recordset AddNew primary key issue

P: n/a
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

Aug 10 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a

sud...@gmail.com wrote:
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
use DCount to see if it exists. if it does, use an update query,
otherwise, insert.

Aug 10 '06 #2

P: n/a

pi********@hotmail.com wrote:
sud...@gmail.com wrote:
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

use DCount to see if it exists. if it does, use an update query,
otherwise, insert.
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...

Aug 10 '06 #3

P: n/a
use DCount to see if it exists. if it does, use an update query,
otherwise, insert.

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

Aug 10 '06 #4

P: n/a
* su****@gmail.com:
pi********@hotmail.com wrote:
>sud...@gmail.com wrote:
>>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
use DCount to see if it exists. if it does, use an update query,
otherwise, insert.

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...
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.
Aug 10 '06 #5

P: n/a

Randy Harris wrote:
* su****@gmail.com:
pi********@hotmail.com wrote:
sud...@gmail.com wrote:
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
use DCount to see if it exists. if it does, use an update query,
otherwise, insert.
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...

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.
ok, thanks!

Aug 11 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.