ADODB & auto values in an MS Access table (+100 points for solving the problem!)

After the Rs.Update, do Rs.Move 0 this should move the record pointer without moving it and should allow you to then examine the appropriate field value for the identity value.

A bit off the track but,

It is always a good idea to use action queries (for example: INSERT, UPDATE and DELETE) on connection object for this purpose.

if I understood it
I used same approach

rs.update
rs.requery
rs.movelast

because requery changes record position
( provided RS is opened ORDER BY autoID)
also    if it is opened Batch we must updatebatch before requery

also if U use ADO 2   not 2.1 or higher U may meet problems

Another approach is to change the CursorLocation to adUseServer.  So your code should look something like:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
With rs
   .Source = "Select * from Table1"
   .ActiveConnection = "add connection here"
   .CursorType = adOpenDynamic
   .LockType = adLockOptimistic
   .CursorLocation = adUseServer    ' <-- add this line
   .Open Options:=adCmdText
   .AddNew
   .Fields(1).Value = "This is my description at " & CStr(Now)   ' This is a text field
   .Update
   Debug.Print .Fields(0).Value   'This is the AutoNumber field
   .Close
End With
Set rs = Nothing

Anthony

TimCottee:
The RS.move 0 has no effect. The auto value is always 0 and therefore still unset.

Bahnass:
Yes. The RS.requery changes the record position.
But my RS is not ordered by the autoID and therefore your approach:
rs.update
rs.requery
rs.movelast

works not well. (But this i could change ... no problem)
But when you delete some datasets your approach fails fully
althought the rs is ordered. The newly created dataset will be created in the gap.

Wolfgang, it works perfectly for me, this is the way that I have done it many times.    

        .AddNew
        !Text = "Hello"
        .Update
        .Move 0
        MsgBox .Fields(0).Value

Is a snippet I just used to test this again.

Wolfgang,

If you use ADO ver 2.1 or higher against an Access 2000 database and version 4.x of the Jet OLE DB provider, it will work automatically. You should also make sure to use a client-side cursor and optimistic locking.

If your application meets all of the above requirements, the autoincrement field will have the correct value after you perform the rs.update.

Ture Magnusson
Karlstad, Sweden

ture,
>>You should also make sure to use a client-side cursor and optimistic locking<<

I think you mean server-side cursor.  Don't you?

If I change the line in the code I posted previously from:
  .CursorLocation = adUseServer    ' <-- add this line
to
  .CursorLocation = adUseClient    ' <-- add this line

I no longer get the ID.

Anthony

I use a client-side cursor and optimistic locking ...
But:
       .AddNew
       !Text = "Hello"
       .Update
       .Move 0
       MsgBox .Fields(0).Value

Results in "0" !

Only after a RS.requery or RS.Close + RS.Open the auto field that i need has an newly created key value < 0.

The descriped problem is a very hard to solved and produces many database error in a greater projekt so i hate auto key values ...

The only but badly way is to set a stamp at the newly created dataset and search for it after the requery ...

Like ture pointed out, if you use ADO and Jet provider, the correct value of the auto field is available after rs.update.

If you want a generic approach for this (SQL server, ORacle):
use bookmark property to reset the cursor position to the current record.
rs.bookmark=rs.bookmark
NewAutoID=rs.fields("YourAutoIDfield")

set cursor type to adOpenKeyset to use the bookmark property.

Hope it helps

Anthony - no, I really mean a client-side cursor.

I made a simple Access 2000 database (c:\test\test.mdb) with a single table fruits (index-autonumber, fruit-text, price-currency) and the code below works very well.

Private Sub Command1_Click()

  'Reference set to MS ActiveX Data Objects 2.5 library
 
  'Declare variables
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim strSql As String
 
  'Initialize and open connection
  Set cn = New ADODB.Connection
  cn.CursorLocation = adUseClient
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test.mdb"
 
  'Initialize and open recordset
  strSql = "select * from fruits"
  Set rs = New ADODB.Recordset
  rs.Open strSql, cn, , adLockOptimistic
 
  'Add a record
  rs.AddNew
  rs.Fields("fruit").Value = "apple"
  rs.Fields("price").Value = 1.25
  rs.Update
  'Show the value of the auto-increment field "index"
  Debug.Print rs.Fields("index").Value
 
  'Add one more record
  rs.AddNew
  rs.Fields("fruit").Value = "banana"
  rs.Fields("price").Value = 1.5
  rs.Update
  'Show the value of the auto-increment field "index"
  Debug.Print rs.Fields("index").Value
 
  'Close recordset and connection
  rs.Close
  cn.Close
 
  'Release object variables
  Set rs = Nothing
  Set cn = Nothing
 
 
End Sub

/Ture

Please note - there is no rs.Move 0 in my code above. Just check the field value immediately after the update.

/Ture

ture,

Funny I did the same thing but with an Access 97 database.
The other difference is that I only use a Recordset while in your case the Recordset CursorLocation is inherited from the Connection object (the outcome should be the same).

My results:
I could only get a value with adUseServer.

If I get a chance I will try with an Access 2000.
Anthony

WolfgangKoenig

I am not suggesting this is what you want to do, but did you attempt to use a CursorLocation = adUseServer?

Also, is this Access 97 or 2000?

Anthony

Anthony, remember that there is no real difference between adUseServer and adUseClient for access databases as the cursor engine is always local to the client. The database is not a true server so you cannot really differentiate between them. In normal circumstances with access the cursor location should have no effect.

I think that it is important to understand the ADO version and Provider version being used. Then the various tests are fair. As Ture has used ADO 2.5 and Jet provider 4.0 can I suggest that Wolfgang test in the same environment and confirm the situation.

TimCottee

I relaize that, hence my confusion as to why it works with one and not the other.  I am using ADO 2.6 and Jet 4.0, other than that I cannot see any difference.  Perhaps it has to do with the version of Access (what do I know, I have not used it in a long time?)

Confused,
Anthony

Ture:
I have fully take your code and build up a vb project.
Then i have build your "fruits" table and run the code.

The output is:
0
0

And the result is and table with:
1  apple   1,25  
2  banana  1,50

But i haven't the autokey values '1' and '2'

Sucks about this...
WoK

The book Programming ADO by David Sceppa, MS Press - (highly recommended!) writes this regarding the automatic resync of autoincrement fields:

"This feature works with version 4 of the Jet OLE DB Provider but only for Jet 4 with Access 2000-formatted databases. This feature does not work with the Access ODBC driver because the driver doesn't support the SELECT @@IDENTITY query."

/Ture

Wolfgang,

Are you using an Access 2000 database? It will not work with an Access 97 database.

I get the output 1 and 2. If I run again I get 3 and 4. These values reflect the actual values of the field "index" in the table.

/Ture

I use MADO 2.0 Libary and MS Access 8.0 Library ...

Therefore i use Access 97 ... What can i do i don't want to force all custumers to install Access 2000 ...

Wolfgang,

If all database handling and all reports are made in your VB application through ADO, this should not be a problem. Your customers don't need Access 2000 (or any Access version) installed to use an Access database through ADO in a VB application.

But if you really use the MS Access 8.0 library to work with MS Access objects, the customer must have Access installed - or perhaps a freely distributable runtime is enough? I'm not sure about that.

/Ture

Try the VB code with my database, which was created with Access 2000.

http://www.turedata.se/exex/testmdb.zip
(The file is only 8 kB.)

/Ture

Ture:
I can't test your db with Access 97 ... Therefore i would need MS Access 9.0 Library ...

;o)

ture,

Have you tried saving your database as Access 97 and then re-testing your code?  I think you will find it works only with CursorLocation = adUseServer.

Anthony

acperkins:
I will think about this ... Meaning that is an bug in ADO 2.0 ?!

It will take a long step to rebuild my app to Ver 2.1 ...

Anthony,

Ok - I tried it with the database in Access 97 format. My VB code worked but only after I changed the cursorlocation to adUseServer.

/Ture

ture & acperkins:
You will get the points when i checked out that adUseServer
will help ...

This will take a lot of time because my in my app the property is set in the connection object and all app RS depend on the connection ....

It seems that is the solution .... (But a little tricky)
;<)

...and a simple search-and-replace wont help you much?

Check this article:
http://support.microsoft.com/support/kb/articles/Q244/1/36.ASP

WolfgangKoenig,

Perhaps, I am being obtuse, but why do you need to change your code (other than the CursorLocation).  Installing a newer version of ADO should be all you need.

Provider=Microsoft.Jet.OLEDB.3.51 will still work with a newer version of ADO.

Anthony

emoreau,

The problem is that WolfgangKoenig is using ADO 2.0

Anthony

15 Hours to have my mail full !!!!!

As I stated a week ago I had a bad day in a customer place to check a similar problem

It was ADO 2.0

Inside a transaction

record not updated !!!!!

Please update to ADO 2.1 at least !!!!!

Or use My 1st approach

OR Put a stamp ( a field Named New record ) set it true until update & requery and then clear it when taking its auto number

OR


GO to sleep & every thing will be OK !!!!!! (:-)>

Please maintain:

Questions Asked 5
Last 10 Grades Given A A A  
Question Grading Record 3 Answers Graded / 3 Answers Received

WolfgangKoenig:

Do you wish to split the points between ture and acperkins?

Lunchy
Friendly Neighbourhood Community Support Moderator

Hi Lunchy, I do not see him comming back.  This happened with him once before, so I'll just do it for him and report him to admin for the other Q's also.

I have reduced the points from 256 to 128 for the split and accepted acperkins here, and will create a question for ture in this Topic Area for 128 points.

Thanks all,
ComTech
Community Support Moderator

Sorry i had a lot to do an sometimes teh experts-exchange server was down ...

Ture:
I have send a question:
Points for ture

WoK