>> adodb.recordset last updated

  • Thread starter Thread starter Jonathan
  • Start date Start date
J

Jonathan

Hi, using Access 2003. A dao.recordset has a property lastModified that
returns a bookmark indicating the most recently added or changed record. I
can't see an equivalent ado.recordset property.

The problem is that a sql record does not have an id until after the record
is inserted (in access once the first field is inserted an id is assigned). I
would like to insert a record and get the id for this new record. Depending
upon the sort, moveLast may not return the id for the new record.

Is there a property or method that I can use to return the id for the most
recently added or changed record?

Any idea or suggestion appreciated :-)
Many thanks,
Jonathan
 
try this

dim LastId as long
.....
.....
your insert statement
LastId = IdActual("tableJustUsed")
.....
rest of your code
.....

Public Function IdActual(ByVal tabla As String) As Long
Dim rst As ADODB.Recordset
Set rst = CurrentProject.Connection.Execute("SELECT IDENT_CURRENT ('" &
tabla & "') AS Current_Identity")
IdActual = rst!current_identity
rst.Close
Set rst = Nothing
End Function

you can replace IDENT_CURRENT with SCOPE_IDENTITY, depending your
environment or needs.

see you
 
Another option would be to simply read the value back from the fields
collection *after* the Update (and not before as it is when working with
JET):

Set rs = New ADODB.Recordset
' Default value for CursorLocation: adUseServer
rs.CursorLocation = adUseClient
rs.Open " -- Your Select query here -- ", , adOpenStatic, adLockOptimistic

rs.AddNew
rs("Name1") = Me.Name1
rs.Update

' IdTable is the identity field:
IdTable = rs("IdTable")

rs.Close
Set rs = Nothing

Notice that this won't work will all types of recordset; so if you change
one of the used options above (adUseClient, adOpenStatic, adLockOptimistic),
it might not work anymore. I don't remember all the possible working
combinations here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
 
Back
Top