By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,475 Members | 954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,475 IT Pros & Developers. It's quick & easy.

Obtaining Primary Key when Updating with ADO

P: 5
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.
Aug 7 '07 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
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. :)
Aug 7 '07 #2

P: 5
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.
Aug 7 '07 #3

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

Expand|Select|Wrap|Line Numbers
  1. Sub adotest()
  2. Dim cnn As New ADODB.Connection
  3. Dim rst As New ADODB.Recordset
  4. Dim sSQL As String
  5.     cnn.ConnectionString = sADOConn
  6.     cnn.Open
  7.     rst.Open "review", cnn, adOpenDynamic, adLockPessimistic
  8.     rst.AddNew
  9.     Debug.Print "1:" & rst("review_id")
  10.     rst("review_date") = Now()
  11.     rst("category") = "Building"
  12.     rst("review_code") = "ADO1"
  13.     rst("review_description") = "Test"
  14.     rst("review_comments") = "Test"
  15.     rst("review_owner") = "it"
  16.     Debug.Print "2:" & rst("review_id").Value
  17.     rst.Update
  18.     rst.MoveLast
  19.     Debug.Print "3:" & rst("review_id").Value
  20.     rst.Close
  21.     cnn.Close
  22. End Sub
And the output is as follows:
Expand|Select|Wrap|Line Numbers
  1. 1:
  2. 2:
  3. 3:34
My connection string is:
Expand|Select|Wrap|Line Numbers
  1. DRIVER=Microsoft Access Driver (*.mdb);
  2. UID = admin;
  3. UserCommitSync = Yes;
  4. Threads = 3;
  5. SafeTransactions = 0;
  6. PageTimeout = 5;
  7. MaxScanRows = 8;
  8. MaxBufferSize = 2048;
  9. FIL=MS Access;
  10. DriverID=281;
  11. DefaultDir=[folder_name];
  12. 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.
Aug 8 '07 #4

P: 5
Problem solved! I realised it must have been in the connection string, and using
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 8 '07 #5

FishVal
Expert 2.5K+
P: 2,653
OK, I've tried it again this morning and it isn't working. This is a little test that I've tried:

Expand|Select|Wrap|Line Numbers
  1. Sub adotest()
  2. Dim cnn As New ADODB.Connection
  3. Dim rst As New ADODB.Recordset
  4. Dim sSQL As String
  5.     cnn.ConnectionString = sADOConn
  6.     cnn.Open
  7.     rst.Open "review", cnn, adOpenDynamic, adLockPessimistic
  8.     rst.AddNew
  9.     Debug.Print "1:" & rst("review_id")
  10.     rst("review_date") = Now()
  11.     rst("category") = "Building"
  12.     rst("review_code") = "ADO1"
  13.     rst("review_description") = "Test"
  14.     rst("review_comments") = "Test"
  15.     rst("review_owner") = "it"
  16.     Debug.Print "2:" & rst("review_id").Value
  17.     rst.Update
  18.     rst.MoveLast
  19.     Debug.Print "3:" & rst("review_id").Value
  20.     rst.Close
  21.     cnn.Close
  22. End Sub
And the output is as follows:
Expand|Select|Wrap|Line Numbers
  1. 1:
  2. 2:
  3. 3:34
My connection string is:
Expand|Select|Wrap|Line Numbers
  1. DRIVER=Microsoft Access Driver (*.mdb);
  2. UID = admin;
  3. UserCommitSync = Yes;
  4. Threads = 3;
  5. SafeTransactions = 0;
  6. PageTimeout = 5;
  7. MaxScanRows = 8;
  8. MaxBufferSize = 2048;
  9. FIL=MS Access;
  10. DriverID=281;
  11. DefaultDir=[folder_name];
  12. 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.
Aug 8 '07 #6

P: 5
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!
Aug 8 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.