ADO Recordset

[JimS]

Using an ADO recordset, I issue an AddNew, then populate, then issue an
Update. If the primary key is an autonumber field, how do I know what that
new column value is? Will MoveLast do it? Or does the recordset pointer
remain on the record I just updated?

 

[Douglas J. Steele]

Haven't tested with ADO, but with DAO, you can simply refer to the
Autonumber field to find out its value.

rs.AddNew
rs!Field1 = "abc"
Msgbox "I just added a record with Autonumber " & rs!Id

 

[vanderghast]

With DAO, you have the automatically generated key after the AddNew, but if
you don't capture it, be aware that after the Update, the recordset will
likely be focused at the record you were before the AddNew.
With ADO, you can read the key after the AddNew since the record with the
focus will be the one you just added.



The following code illustrates the behavior about which record is the
current record after you append a new record:

===================
Public Sub ADODAO()
Dim rst As DAO.Recordset
Dim uvw As New ADODB.Recordset
Set rst = CurrentDb.OpenRecordset("Table1")

uvw.Open "Table1", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable
uvw.MoveLast
uvw.MovePrevious
Debug.Print "ADO, we start with: " & uvw.Fields("f1").Value
uvw.AddNew
uvw.Fields("f1") = "new field"
uvw.Update
Debug.Print "ADO, after the Update, we are at: " &
uvw.Fields("f1").Value

rst.MoveLast
rst.MovePrevious
Debug.Print "DAO, we start with: " & rst.Fields("f1").Value
rst.AddNew
rst.Fields("f1") = "DAO's one"
rst.Update
Debug.Print "DAO, after the Update, we are at: " &
rst.Fields("f1").Value
End Sub
=====================

and, in my case, I got:


-------------------Immediate Window
ADODAO
ADO, we start with: ab
ADO, after the Update, we are at: new field
DAO, we start with: a
DAO, after the Update, we are at: a

 

[vanderghast]

I meant

With ADO, you can read the key after the ***UPDATE**** since the record
with the
focus will be the one you just added.

and not after the ***AddNew***


Vanderghast, Access MVP

 

[David W. Fenton]

Haven't tested with ADO, but with DAO, you can simply refer to the
Autonumber field to find out its value.

rs.AddNew
rs!Field1 = "abc"
Msgbox "I just added a record with Autonumber " & rs!Id

Depends on your back end, of course. If it's Jet, yes, you'll have
the value immediately. If it's SQL Server, you won't.

But, of course, if it's SQL Server, it's not an Autonumber.

 

[David W. Fenton]

With ADO, you can read the key after the ***UPDATE**** since the
record
with the
focus will be the one you just added.

and not after the ***AddNew***

And, of course, there's always:

SELECT @@IDENTITY

that could be used after the insert is done. This is paricularly
useful in cases where you choose to use a SQL INSERT instead of a
recordset to add data -- execute the INSERT and then immediately ask
for the identity value, and you'll be done. In fact, you can do
this:

lngNewID = db.OpenRecordset("SELECT @@IDENTITY")(0)

Of course, you have to be certain you use the same database variable
that you used for Executing the INSERT. That is, you can't use
CurrentDB for the execute followed by the statement above with
CurrentDB in place of the db variable.