Obtaining Primary Key when Updating with ADO

[brydend]

I'm trying to return the AutoNumber Primary key using ADO and I'm struggling. In DAO, before you update, you can retrieve the autonumber that has been generated. Unfortunately I'm on Jet 3.0 so I can't use the @@IDENTITY statement.
I can move to the end of the recordset after update, but it is a multi user application and I can't be sure that the last one will return to the correct number.
I'm sure that there must be a solution to this - can anyone help?

Many thanks,



Daniel.

[FishVal]

I'm trying to return the AutoNumber Primary key using ADO and I'm struggling. In DAO, before you update, you can retrieve the autonumber that has been generated. Unfortunately I'm on Jet 3.0 so I can't use the @@IDENTITY statement.
I can move to the end of the recordset after update, but it is a multi user application and I can't be sure that the last one will return to the correct number.
I'm sure that there must be a solution to this - can anyone help?

Many thanks,



Daniel.

Hi, Daniel.

In ADO Autonumber field is available immediately after you've invoked Recordset.AddNew method. BTW after you've invoked Recordset.Update method cursor remains on the record, so the field is available as well.

Can't understand your difficulties. :)

[brydend]

I can't either! I've just tried it on 2003 and it works fine. I'll check at work again tomorrow and see what mistake I've made. Thanks for the help.

[brydend]

OK, I've tried it again this morning and it isn't working. This is a little test that I've tried:

Sub adotest()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sSQL As String
    cnn.ConnectionString = sADOConn
    cnn.Open
    rst.Open "review", cnn, adOpenDynamic, adLockPessimistic
    rst.AddNew
    Debug.Print "1:" & rst("review_id")
    rst("review_date") = Now()
    rst("category") = "Building"
    rst("review_code") = "ADO1"
    rst("review_description") = "Test"
    rst("review_comments") = "Test"
    rst("review_owner") = "it"
    Debug.Print "2:" & rst("review_id").Value
    rst.Update
    rst.MoveLast
    Debug.Print "3:" & rst("review_id").Value
    rst.Close
    cnn.Close
End Sub

And the output is as follows:

1:
2:
3:34

My connection string is:

DRIVER=Microsoft Access Driver (*.mdb);
UID = admin;
UserCommitSync = Yes;
Threads = 3;
SafeTransactions = 0;
PageTimeout = 5;
MaxScanRows = 8;
MaxBufferSize = 2048;
FIL=MS Access;
DriverID=281;
DefaultDir=[folder_name];
DBQ=[database_name]

Any thoughts? When I was playing on Access 2003 last night, I did manage to recreate this using a different connection string. I'm running this in Access 97, ActiveX Data Objects 2.5, Jet 3.0.
Thanks for your help.

[brydend]

Problem solved! I realised it must have been in the connection string, and using

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=[filename]; User Id=admin; Password=

has fixed it nicely.
I'm assuming that it isn't available in the older version of the Access driver.
Thanks for the help previously; this is now closed.

[FishVal]

OK, I've tried it again this morning and it isn't working. This is a little test that I've tried:

Sub adotest()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sSQL As String
    cnn.ConnectionString = sADOConn
    cnn.Open
    rst.Open "review", cnn, adOpenDynamic, adLockPessimistic
    rst.AddNew
    Debug.Print "1:" & rst("review_id")
    rst("review_date") = Now()
    rst("category") = "Building"
    rst("review_code") = "ADO1"
    rst("review_description") = "Test"
    rst("review_comments") = "Test"
    rst("review_owner") = "it"
    Debug.Print "2:" & rst("review_id").Value
    rst.Update
    rst.MoveLast
    Debug.Print "3:" & rst("review_id").Value
    rst.Close
    cnn.Close
End Sub

And the output is as follows:

1:
2:
3:34

My connection string is:

DRIVER=Microsoft Access Driver (*.mdb);
UID = admin;
UserCommitSync = Yes;
Threads = 3;
SafeTransactions = 0;
PageTimeout = 5;
MaxScanRows = 8;
MaxBufferSize = 2048;
FIL=MS Access;
DriverID=281;
DefaultDir=[folder_name];
DBQ=[database_name]

Any thoughts? When I was playing on Access 2003 last night, I did manage to recreate this using a different connection string. I'm running this in Access 97, ActiveX Data Objects 2.5, Jet 3.0.
Thanks for your help.

Hmm. You are right, the code seems ok. Maybe smthng A97 specific.
Suggestions:

• After Recordset.Update method invoking cursor stays at the new record. Check whether at this point AutoNumber field contains valid value.
• You can use Recordset.Bookmark property to store record identity.

[brydend]

No worries Fishval, it's all sorted. It was bizarrely the driver, and now everything is working perfectly. There'll be no more DAO for me!