Return AutoNumber Key

[ProfSchiavo]

I'm struggling with a similar problem: a multi-user database, with "Autonumbered" primary key. When user 'a' adds a record, how does she retrive the actual PK entered? We can NOT assume it's the latest record added, because other users are adding records at nearly the same time.

How handy it'd be if dataadapter.insert would return a reliable vector of key(s) for the record(s) added.

Is there any other way? Or must I just do a selection on every field I've just entered? "Select * from MyTbl where fld1=MyFld1 and fld2 = MyFld2 and ... "

???

Thanks,
S

[NeoPa]

I'm afraid that, as this question is NOT the same as the original one (though I recognise there are similarities), then you need to post your question in its own new thread. It's perfectly acceptable to include a link to this thread if you feel it may help to clarify your question.

MODERATOR.

[ProfSchiavo]

Both and advantage and a disadvantage of the 'autonumber' keys provided by MS Access and others is that they are assigned at the host database, not at the dataset. That means, as you have seen, that when you do a dataadapter.update you new records are added with the next autonumber, but you may not really know what that was. Even if you select the highest autonumber yet assigned, it may be one some other user has since assigned -- then there's always the case where you have configured your 'autonumber' key to be randomly, not sequentially assigned, so there's even less assurance that it's the 'highest' key.

What's a multi-user programmer to do? You might assign some alternate unique key (based on userid and timestamp, for instance) such that, once the new record is added, you can retrieve it by the timestamp you just put on it yourself -- then you will learn what the actual autonumber key is, if you still need it.

The ideal, to my way of thinking, would be for the database engine to return a table of new record key(s) for the record(s) added, when you do an insert or an update which adds new records. But then, they never seem to ask my opinion ;^))

[NeoPa]

This discussion has been split away from the access message box/ VB code thread.

In fact it's an interesting point you make. Possibly the AddNew() method should return the value of any AutoNumber index that's created. Unfortunately (as is usually the case) things are not that simple. What about when adding a bunch of new records. The SQL can't return all the keys created.

The real answer is that you're trying to work with it in a way that it isn't designed to work. An automatically assigned field is there for supporting a table with unique keys. If it weren't created - how would you access the data you just added? That wouldn't change. That's not what it's there for.

Does that help?