A problem with getting ID of a just inserted record

[Yarik]

Hello,

Here is the typical code pattern (after inserting a record into a table
with identity column, the code tries to retrieve identifier of that
record; the identity column is called "ID"):

Set rs = New Recordset
rs.CursorLocation = adUseClient
rs.Open "select top 0 * from SomeTable", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

rs.AddNew
rs("NonIdentityField") = "SomeValue"
rs.Update

NewRecordID = rs("ID")

This code used to work fine in Access 2000. In Access 2003, rs("ID")
returns zero instead of the actual ID of the just inserted record. Is
it a bug of Access 2003, or is it a feature?

FWIW, my experiments showed that this problem occurs only if the cursor
behind recordset is client-side. So, as a workaround, I can simply use
the server-side cursor. However, I do not like this workaround as a
long-term solution, because it turns out that a server-side recordset
based on Access 2003 "built-in" connection has some other quirks. For
example, an attempt to get a value of a non-nullable field after AddNew
generates an exception (instead of returning Empty like Access 2000's
connection does and like "vanilla" SQLOLEDB connection does).

Any comments/advices (especially from Microsoft folks would be
greatly appreciated.

Thank you,
Yarik.

 

[Pieter Wijnen]

use the bookmark & lastmodified properties :
Rs.BookMark = Rs.LastModified

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

HTH

Pieter

 

[Yarik]

I'm sorry, I forgot to mention explicitly that I am talking about ADO,
not about DAO. So the "classic" DAO idiom does not apply.

 

[Sylvain Lafontaine]

In case of a client cursor and ADO, always use adOpenStatic instead of
adOpenDynamic.

 

[Yarik]

In case of a client cursor and ADO, always use adOpenStatic instead of
adOpenDynamic.

Well, another funny thing with this problem is that it does not matter
whether the cursor is requested to be static or dynamic - in the code
shown above, after rs.Open() it actually *is* adOpenStatic! And it
still does not obey the protocol (does not keep the just inserted
record current after a call to Update).

Maybe the fact that the recordset is adOpenStatic even though I ask it
to be adOpenDynamic
is just another manifestation of some bug?

Thank you,
Yarik.

 

[Yarik]

And it still does not obey the protocol (does not keep the just inserted
record current after a call to Update).

Well, that wasn't a correct statement, sorry. I did not check whether
it keeps the current record properly. To be absolutely precise, the
problem is the failure to return correct value of the identity field of
the just inserted record.

 

[Sylvain Lafontaine]

Your code work correctly here. Maybe you have forgotten to write
ADODB.Recordset instead of Recordset.

For the client side cursor, it is mandatory to be of type adOpenStatic; even
if you ask for another type. This is an ADO thing.

 

[Yarik]

Your code work correctly here. Maybe you have forgotten to write
ADODB.Recordset instead of Recordset.

You're right, I did forget something... When trying to minimize the
amount of "problem-demo" code, I omitted one detail that turns out to
be crucial: using a separate connection.

I am sorry for the misleading initial code fragment.

Anyway, here is the exact code that demonstrates the issue:


Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Call conn.Open(CurrentProject.Connection.ConnectionString)

Call conn.BeginTrans

' ... Other code in transaction

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
Call rs.Open("select top 0 * from Test", conn, adOpenStatic,
adLockOptimistic)

Call rs.AddNew

' Set values for all required fields
' (except the autoincrementing "ID" field)
rs("Text") = "Text"
rs("Number") = 1

Call rs.Update

' Oops, rs("ID") returns invalid value
Debug.Print "New ID: " & rs("ID")

Call rs.Close

' ... Other code in transaction

Call conn.CommitTrans

Call conn.Close


The rs("ID") returns 0 when CursorLocation is adUseClient.

FWIW: The reason of opening a separate connection is because there is a
whole transaction there (of which adding a new record is just a part);
however, removing BeginTrans and CommitTrans from the code above does
not have any effect: the problem is all the same. BTW, this code is
sort of a legacy code for me, so I do not know what was exact reason of
the separate connection's borrowing its connection string from the
CurrentProject's connection; my guess is, it was the easiest way to
encapsulate certain connection settings (database name, security
settings, etc.) in one place - in the settings of the ADP project.

Sincerely,
Yarik.

 

[Sylvain Lafontaine]

You're right, your new piece of code won't work because the connection
string provided by CurrentProject.Connection uses the special provider
Microsoft.Access.OLEDB.10.0, see http://support.microsoft.com/kb/281784/ .

You must either provide your own connection string or use the global
CurrentProject.Connection instead of creating a new connection.

 

[Yarik]

You're right, your new piece of code won't work because the connection
string provided by CurrentProject.Connection uses the special provider
Microsoft.Access.OLEDB.10.0, see http://support.microsoft.com/kb/281784/ .

You must either provide your own connection string or use the global
CurrentProject.Connection instead of creating a new connection.

THANKS A LOT!

Sincerely,
Yarik.