Y
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.
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.