Using Addnew with a disconnectd recordset.

[thornmastr]

i am using the recordset.addnew method to add a new record to a disconnected recordset and all works well. However, once the update batch completes, I need to get the autonumber value assigned to the record. The code which both updates the record and gets the identity is below. The value returned by the identity is always 0 which is obviously incorrect. I have heard the following as possible explanations.

1. “select @@identity” is valid only after an INSERT statement. On the face of it, this seems patently absurd.
2. Specific to ACCESS, @@Identity is valid only for A2K and above. Since I am using an A97 database, even though I am using JET 4 SP 7, A97 negates using @@Identity. In my opinion this is as absurd as reason 1 but somehow, looking at M$ it is a distinct possibility.

Given this dilemma is there a relatively simple method to get the value for the last autonumber applied to the distinct last recordset updated. Given the natural constraints of the connection, this should not be so difficult.

Comments, suggestions, criticisms are all welcome.

CODE


Public Sub Update_Residency()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, sCon As String

If Key.Text = "" Then
sSQL = "Select * from tblresidences"
Else
sSQL = "Select * from tblresidences where PKResident = " & Key
End If

Set cn = New ADODB.Connection
cn = constr ‘constr is created when program first starts and is the connection string”
cn.Open
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.Open sSQL, cn
sCon = rs.ActiveConnection
Set rs.ActiveConnection = Nothing
If Key = "" Then
rs.AddNew
End If

rs("fkstudent&quot = keyinuse
rs("lastname&quot = Me("lastname&quot
rs("firstname&quot = Me("firstname&quot
If Me(&quot;MI&quot <> &quot;&quot; Then
rs(&quot;MI&quot = Me(&quot;mi&quot
End If
rs(&quot;street1&quot = Me(&quot;street1&quot
rs(&quot;street2&quot = Me(&quot;street2&quot
rs(&quot;city&quot = Me(&quot;city&quot
rs(&quot;state&quot = Me(&quot;state&quot
rs(&quot;zipcode&quot = Me(&quot;zipcode&quot
rs(&quot;homephone&quot = Me(&quot;homephone&quot
rs(&quot;workphone&quot = Me(&quot;workphone&quot
rs(&quot;pager&quot = Me(&quot;pager&quot
rs(&quot;cellphone&quot = Me(&quot;cellphone&quot
rs(&quot;emailaddress&quot = Me(&quot;emailaddress&quot
rs(&quot;pager&quot = Me(&quot;pager&quot
rs(&quot;cellPhone&quot = Me(&quot;cellphone&quot
rs.ActiveConnection = sCon
rs.UpdateBatch
rs.Close
rs.Open &quot;select @@Identity&quot;, cn, adUseClient, adLockBatchOptimistic
Me.Key.Text = rs(0)
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

END OF CODE

Thank you,


Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@earthlink.net

 

[CCLINT]

The recordset UpdateBatch will ALSO retreive the Identity for the LAST recorded affected through the batch update by issuing a SELECT @@IDENTITY statement itself, and you only need to call rs(&quot;myIDField&quot after the UpdateBatch (and before the close statement) to retrieve that value. So the extra
rs.Open &quot;select @@Identity&quot;, cn, adUseClient, adLockBatchOptimistic
is not needed.
(Anyways, you would be better off to use
Set rs = cn.Execute (&quot;select @@Identity&quot instead)

You would only need this after using an Action INSERT query.

The fact that it retreives only the ID for the last inserted record on that particular connection, is not only correct, but exactly what I would expect, considering the purpose of, and difficulty managing, an Identity field in a multi-user environment - it has to remain unique.

The problem being, the JET 3/4 dbms does not retreive the ID of a record being added to a JET 3 MDB, for what ever reasons. Only in combination using JET 4 dbms and a JET 4 Db.

The only solution I can offer, outside of creating a SECOND unique key which holds the user name as well as a time stamp (then you can use this to find the record and get the AutoID value of the newly inserted record).

Or, just requery the recordset prior to disconnecting it again after the Update, and issue a MoveLast, and hope that the last record really is the last record added by the client running this requery (compare some other fields).

Or try write locking the table between the time you call UpdateBatch, and have returned a second recordset from a seperate query in order to get the ID value of the last added record.

 

[thornmastr]

CCLINT,

Thank you for the explanation.

I began searching for a method to get the identity of the last record without consuming too many resources. Since each record has a forward key as well as a primary key and I do know the value for the forward key, I used the following logic after the batch update to get the identity of the newly added record.

rs.UpdateBatch
rs.Close
sSQL = &quot;SELECT top 1 fkstudent,pkresident from tblresidences where fkstudent = &quot; & keyinuse & &quot; order by fkstudent desc&quot;
rs.Open sSQL, cn, adOpenStatic, adLockOptimistic
Me.Key.Text = rs(&quot;pkresident&quot
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

The only time this could cause a problem if two users were updating the residency record for the same student which is an extremely low probability.

Again, thank you for the explanation.



Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@earthlink.net

 

[CCLINT]

Good. Therefore I said:
&quot;outside of creating a SECOND unique key which holds the user name as well as a time stamp

Under JET 3 this is safest way to do this, (or use some another unque key), as the same TimeStamp and unique UserID shouldn't be possible elsewhere at that moment.
Then you can retrieve the newly added record's AutoID value by using the second key in the criteria.

This would seem to me better than locking the table between updating and getting the Top record.

IMO, a TimeStamp and UserID should be used anyways...