Autonumbers in SQL Server Recordsets

[Guest]

In Access, an autonumber is generated as soon as you change any field of
the record whereas SQL Server doesn’t set the autonumber until the record is
saved. In multiple places in my code, I use the Addnew method to create a
new record, set the value of a field (for example RS!VendorName = “Altekâ€),
and then, right after setting the value of the first field, save the
autonumber value to a variable for future use. How can I access the value of
a new record's autonumber field for a recordset made from a SQL Server table?
I tried to access the autonumber value after I issued the update method to
the recordset, but the focus is set to the first record of the table after an
update, and I get whatever the autonumber of the first record is.

 

[Sylvain Lafontaine]

Hum, this newsgroup is about ADP and not about DAO or ODBC linked tables; so
you should ask in a more appropriate newsgroup if you want to have the
correct answer; however, in your case, I think that you must use the
rs.LastModified bookmark, something in the line of:

rs.Update
rs.Bookmark = rs.LastModified
PrimaryKey_Identification = rs("PrimaryKey_Identification ")

A second possibility would be to change the nature of the recordset used.
Maybe that by opening another type of recordset, it won't change its
position after the update.

 

[Sylvain Lafontaine]

Hum, not got enough coffee this morning: please disregard my previous
comment about the fact that you might be using DAO instead of ADO. The
LastModified property is not available with ADO recordset, however, this
doesn't mean that an ADO recordset will not change its position after an
update.