Get unique id of new record 2

[TimTDP]

I create a new record in a table using DAO and .addnew & .update

Each record has a unique Id based on a field called "ContactId". This an Autonumber with the increment set to Randon

How can I get the value of this field when the record is created?

 

[jadams0173]

I would think the easiest (maybe not the best) would be to read the value after the update. Before you close the recordset, but after the update, a MoveLast and then read the value and assign the autoid to a variable.

Just a thought/question? Why would you want a unique ID to be based on a random number instead of a sequentially incremented number?

 

[TimTDP]

I use Random because the database is replicated

 

[jadams0173]

Ah...I understand now. Were you able to read the autonumber?

 

[PHV]

You may try something like this:
With rs
.AddNew
...
.Update
.Bookmark = .LastModified
MsgBox "New ID = " & !ContactID
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IronBeaver]

Copied From

http://objectsharp.com/blogs/bruce/articles/210.aspx

Microsoft Access

With Access, you are limited to basically a single technique. On a positive note, the same technique works all the way back to Access 97

First of all, I assume that we are inserting a record into a table where the primary key has an AutoNumber type. The addition of the record must be accomplished by using the AddNew and Update methods of the ADO Recordset object. Then, once the record has been added, store the absolute position of the new record and perform a Requery. Finally, set the cursor back to the bookmarked record and read the Id. Seem like a lot of work? And inflexible to boot? Such is the joy of Access

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "DSN=MyDSN;"

rs.CursorLocation = adUseClient
rs.Open "SELECT CustNo, CustomerName, Contact", cn, adOpenStatic, adLockOptimistic

rs.AddNew

' CustNo is the AutoNumber field
rs.Fields("CustomerName").Value = "ObjectSharp"
rs.Fields("Contact").Value = "Bruce Johnson"
rs.Update

' The record has been inserted, but rs.Fields("CustNo").Value is zero
bookmark = rs.absolutePosition
rs.Requery
rs.absolutePosition = bookmark

' Voila
MsgBox rs.Fields("CustNo").Value




Feedback from article:
-------------------------
# re: Identifying New Records 3/5/2004 6:41 PM Bob Monahon

Thanks for the tip. Here are two for you:
1. If the table is an ACCESS table; using DAO; the key is available between the .AddNew and the .Update:
.AddNew
saveKey = rs.Fields("CustNo").Value
.Update

2. Using your tip; with ADO 2.7; The key is NOT available between .AddNew and .Update, but IS avalailable after the .Update without need for the .Requery:
.AddNew
.Update
saveKey = rs.Fields("CustNo").Value

 

[RoyVidar]

IronBeaver, I'm afraid the article you quoted, has got it a bit wrong. As is commented by mark grizzle there, Select @@Identity works with Jet 4.0. See also for instance thread701-1058773.

I think one should be able to get the last identity reliably in that sample by fetcing the identity just after the .update - just for testing, try this rather crude test just after the .update.

[tt]debug.print cn.execute("Select @@Identity",,adcmdtext).fields(0).value[/tt]

But why open a recordset just to add a record, why not just execute an action query?

I think the sample on that page works for Access 97 and previous versions, and I think I've also seen Microsoft KB articles recommend such for for instance VBScript or similar connecting to previous versions of Access.

I think I'd be a bit reluctant with relying upon the .Absoluteposition property for something like this. Especially, if a .Requery is needed, what happens if other users have added or deleted records at the same time, the where condition excludes the record or the sort order of the query places the record somewhere not intended ... Also the comments indicate the behaviour of this method is a tad to unpredictable for me to use.

Roy-Vidar

 

[Zion7]

Roy, if your suggestion is heeded, (Action Query), how would one obtain the latest inserts, since the Primary key is not sequential?
DMax() doesn't appear to be an option?

DLookup()?

Just curious, Thx!

 

[RoyVidar]

Zion7
- the method presented by PHV for DAO and Select @@Identity (which can also be used through DAO) are the two methods that should provide exactly what you ask (the last identity (autonumber) inserted on the connection), also in a mulitiuser environment.

Do test both the QAD sample above with IronBeavers sample, and the sample in the reference thread.

The rest of the methods, including Domain Aggregates, can be said to have flaws, at least when working with 2000 and higher versions, for instance if other users are inserting or deleting records at the same time ...

Roy-Vidar

 

[Zion7]

Thank you Roy,
It appears I really misunderstood the code at first.
After following your link, your code became more clear, and I realized, you already answered my question.

Sorry,
Thx!