Obtain Autonumber value after record creation.

[ThomasAJ]

When adding a record to a table whose key field is an autonumber field how
can I obtain it's value with 100% confidence.

Sure immediately afterwards I can read the last record in that table and 99%
of the time be sure it's the one I have just created but there is still a
slight posibility that another proceess may have created a new record between
my create and my read.

 

[Douglas J. Steele]

What's the code you're using to populate the table?

 

[ThomasAJ]

set rs = mydb.openrecordset("Table")
rs.addnew
rs!field2 = "abc"
rs.update

Field1 in table is an autonumber key field.
--
Regards
Tom

What's the code you're using to populate the table?

 

[Douglas J. Steele]

Simply refer to rs!Field1 in your code:

MsgBox "AutoNumber value is " & rs!Field1

I'm afraid I can never remember whether you do this before or after the
rs.Update, though. You'll have to play with it in both places to determine
which one works.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

set rs = mydb.openrecordset("Table")
rs.addnew
rs!field2 = "abc"
rs.update

Field1 in table is an autonumber key field.

 

[ThomasAJ]

After testing:

Field1 (the autonumber field) is updated and 'viewable' as soon as ANY field
has a value put into it.

Also yes it needs to be viewed before the UPDATE as after the update there
is no CurrentRecord.

many thanks

Tom

 

[Albert D.Kallal]

Also yes it needs to be viewed before the UPDATE as after the update there

is no CurrentRecord.

Actually, here is a good approach:

rstRecords.AddNew

' code can go here to add data, or set values to the reocord...
........
........

rstRecords.Update

rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID
rstRecords.Close
Set rstRecords = Nothing

MsgBox lngNext

Note how I actually finish the update..and then move the record back to the
updated. This is also nice for code that needs to reference the values in
the reocrdset...(and thus not have to declare extra vars to hold soem
values).

So, often after I do a update, I want the recordsset to REMAIN on the same
record. This gives me use of the auotnumber..and also just keeps the code
on the reocrdset....

In addtion, while when using JET, you can go

rstRecords.AddNew

AT this point, the auto number id is available..even if you have NOT YET
modified any values. However, if you use a server based system like Sql
server, then the autonumber field is NOT available until AFTER the update.
Thus, the above code that uses the lastmodifeed setting is better, since it
will work wit both JET, or sql server (or mysql..or whatever). So, you
likely should adopt a coding standard that executes the update...and THEN
grabs the autonumber field, since this will work with most database systems
from JET...all the way to oracle....

Note well that you can also use a sql statement go get that last number (jet
4...access 2000 or later).

IF you do a update..., either via reordset code, or sql insert statement,
then you can also use

lngLastId = CurrentDb.OpenRecordset("select @@Identity from myTable")(0)

 

[david epsom dot com dot au]

I'm not 100% sure about the ODBC/SQL Server compatible code shown. It seems
possible that the line

Option Compare Binary

might be required at the beginning of the module.

Microsoft has never been entirely clear about this: there has been
conflicting information. I don't have a handy reference, but there has been
documentation indicating Option Compare Binary is required. Most examples
don't mention it, but of course Option Compare Binary was the default for
other versions of Visual Basic.

Since the line

rstRecords.Bookmark = rstRecords.LastModified

implicitly compares bookmarks, and since bookmarks are 'binary' fields, you
can see why Option Compare Binary might be required.

On the other hand, if it was done properly, it should ignore the option
setting at the top of the module.

I have wondered if this might be the source of some of the 'bookmark bugs'
others have seen. Of course the problem would only arise if Access/Jet
generated bookmarks which were identical using "Option Compare Database",
which could easily lead to systematic errors.

(david)

 

[Ron Weiner]

Am I the only guy on the planet that uses ADO?

I have been using code like this for a long time, and never have had a
problem. After the rs.Update the recordset is still sitting on the same
record and the autonumber value is ready to be read.

Set rs = New ADODB.Recordset
rs.Open "SomeTable", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
rs.AddNew
' Set all values here as necessary...
rs.Update
lngNewAutonumberID = rs!AutonumberFieldName
rs.Close
set rs = nothing

 

[Douglas J Steele]

Actually, that was the source of my comment about not remembering when to
reference the rs!AutonumberFieldName: IIRC, it's different in ADO and DAO.

I use ADO for other DBMS (mostly SQL Server), but never for Jet databases. I
prefer DAO with Jet databases.

 

[Ron Weiner]

Geeze How do you keep em' all straight?

You must have a much bigger brain than mine. I couldn't keep DAO, RDO, and
ADO all in my brain at the same time, so I decided to just pick one an go
with it. I have used ADO just about exclusively for the past 5 years or so
only using DAO when there was no (easy) ADO solution. This way I was using
essentially the same code in all of the environments that I use (Access, VB6
classic, ASP classic, and VB Script).

Now as I am moving more of my development into the Dot Net realm, I find
that I am having to stretch my brain (with lots of damaged cells from an
impetuous youth in the 60's) to get the ADO.NET stuff in there. The only
good news has been that the ADO and ADO.NET object models are SOooo...
different that I rarely get confused. I do however wonder what data access
alphabet soup will come next from mSoft. Are we ready for... TEN.ODA?

 

[Albert D.Kallal]

Am I the only guy on the planet that uses ADO?

A nice little code example you posted. I have OFTEN said that ADO is newer,
and OFTEN cleaner then DAO.

Further, with ado you don't even have to issue a update..but can simply move
on.

And, I can't tell you the number of times I had code where I want to
update...and STAY on the same record. For sure. this is one area that ADO
got right......

however, by habit...I still use DAO in ms-access!!!

 

[david epsom dot com dot au]

Regardless of which method you use, Jet creates autonumbers locally,
writing them back to the database, and SQL Server creates identities
centrally, writing them back to the client.

So for SQL Server tables you have to use a method that finds
the correct record,

And for Jet tables there is always the slight posibility that
another process may have created the same autonumber value.

To avoid this, you have to write a Jet autonumber value back to the
database as quickly as possible, or use your own autonumber code.

With autonumbers, that means updating the record and /flushing
the cache/.

For more information,
How To Implement Multi-user Custom Counters in DAO 3.5

http://support.microsoft.com/kb/q191253/

There is a dao 3.6 version of that somewhere, but it is substantially the
same.

(david)