Getting the autonumber value after adding new record when dao points to SQL rather than JET

[Tony Epton]

Access 2003, SQL 2000, Windows XP


I usually worked in DAO and JET and have the luxury of being able to
do the following:

-----------------------------------------
dim lngClientId as long
dim rs as recordset

set rs = currentdb().openrecordset("tblClient")
rs.addnew
rs![Surname] = "test surname"
rs![FirstName] = "test firstname"
lngClientId = rs![ClientId] 'ClientId is an autonumber field
rs.update
rs.close
set rs = nothing
---------------------------------------

I end up with the value of the autonumber field after adding a new
record.

Of course this does not work for a SQL back end.

Possibilities that spring to mind are :
====
1
====

set rs = currentdb().openrecordset _
("select ClientId from tblClient order by ClientId")
if rs.bof and rs.eof then
lngClientId = 0
else
rs.movelast
lngClientId = rs![ClientId]
endif
rs.close
set rs = nothing

Though this does run the risk of another user adding another record in
between.

====
2
====
or opening a recordset that filters on many of the values in the
client record to find only the record I just added - but this would be
slow or would need a large index overhead.

I'm sure this question has been answered many times - but I cannot
find the right article when I search the knowledge base.
Could someone point me in the right direction please.


I would be prepared to use a different method (ADO) if this would
help.

Many thanks
Tony Epton

 

[Tom van Stiphout]

On Tue, 04 Dec 2007 02:16:31 GMT, ace join_to (e-mail address removed) (Tony
Epton) wrote:

I didn't think your code would work even with an MDB backend.
According to the docs, you should write:
rs.addnew
rs![Surname] = "test surname"
rs![FirstName] = "test firstname"
rs.update
rs.Move 0, rs.LastModified
lngClientId = rs![ClientId] 'ClientId is an autonumber field

-Tom.

 

[Tony Toews [MVP]]

I didn't think your code would work even with an MDB backend.

Oddly enough it does although I didn't know that until someone
mentioned this a few years ago. Possibly Jet 4.0 changed this
behavior but I"m not at all sure of this.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Epton]

Many thanks Tom
You are a lifesave

Tony

I didn't think your code would work even with an MDB backend.
According to the docs, you should write:
rs.addnew
rs![Surname] = "test surname"
rs![FirstName] = "test firstname"
rs.update
rs.Move 0, rs.LastModified
lngClientId = rs![ClientId] 'ClientId is an autonumber field

-Tom.

Access 2003, SQL 2000, Windows XP


I usually worked in DAO and JET and have the luxury of being able to
do the following:

-----------------------------------------
dim lngClientId as long
dim rs as recordset

set rs = currentdb().openrecordset("tblClient")
rs.addnew
rs![Surname] = "test surname"
rs![FirstName] = "test firstname"
lngClientId = rs![ClientId] 'ClientId is an autonumber field
rs.update
rs.close
set rs = nothing
---------------------------------------

I end up with the value of the autonumber field after adding a new
record.

Of course this does not work for a SQL back end.

Possibilities that spring to mind are :
====
1
====

set rs = currentdb().openrecordset _
("select ClientId from tblClient order by ClientId")
if rs.bof and rs.eof then
lngClientId = 0
else
rs.movelast
lngClientId = rs![ClientId]
endif
rs.close
set rs = nothing

Though this does run the risk of another user adding another record in
between.

====
2
====
or opening a recordset that filters on many of the values in the
client record to find only the record I just added - but this would be
slow or would need a large index overhead.

I'm sure this question has been answered many times - but I cannot
find the right article when I search the knowledge base.
Could someone point me in the right direction please.


I would be prepared to use a different method (ADO) if this would
help.

Many thanks
Tony Epton