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

 

[JimBurke via AccessMonster.com]

after you do the update, but before you close the recordset, that field name
will have the new value, e.g.

rst.AddNew
rst!someField = someValue
rst.Update
msgbox "the new value is " & rst!IDfieldName
rst.Close

 

[JimBurke via AccessMonster.com]

Whoops, my mistake. For some reason I had it in my head that you had to wait
til the Update, but like V said, once you've done the AddNew you can
reference the value.

 

[JimBurke via AccessMonster.com]

I missed his last post! But I did a quick test and was able to reference the
value before the Update was done. When you're adding a new record via the
table directly (on the access Tables tab) Access waits until you've started
actually entering data before it fills in the autonumber field. But in my
quick test I did a msgbox of the !ID field right after the AddNew in my VBA
code and it displayed the value.

Whoops, my mistake. For some reason I had it in my head that you had to wait
til the Update, but like V said, once you've done the AddNew you can
reference the value.

after you do the update, but before you close the recordset, that field name
will have the new value, e.g.

[quoted text clipped - 9 lines]

 

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