We help IT Professionals succeed at work.

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

WolfgangKoenig
on
I have an access table with an autovalue as primary key.
The problem is now when i create a new dataset with the command:

Dim RS As Recordset
RS.AddNew
RS.Update

i need to have the generated autokey value from the underlaying db table. Because other tables have references to the primary key. At this moment the autokey value is 0.

When i make a:

RS.Requery

the RS will be 'rebuild' and i don't find the dataset that i have added before. (Beside the autokey value is now set to a correct value)

At the moment a haven't found a properly method.
Comment
Watch Question

TimCotteeHead of Software Services
CERTIFIED EXPERT

Commented:
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.
Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
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.

Commented:
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
CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Author

Commented:
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.






TimCotteeHead of Software Services
CERTIFIED EXPERT

Commented:
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.

Commented:
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
CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Author

Commented:
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 ...

Author

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

Commented:
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

Commented:
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

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

/Ture
CERTIFIED EXPERT
Top Expert 2012

Commented:
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
CERTIFIED EXPERT
Top Expert 2012

Commented:
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
TimCotteeHead of Software Services
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Author

Commented:
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

Commented:
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

Commented:
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

Author

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

Author

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

Commented:
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

Commented:
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
CERTIFIED EXPERT
Top Expert 2012
Commented:
Wolfgang,

At least get ADO 2.1, this should solve it. Take a look at the following articles:

FIX: AutoNumber Field Is Not Incremented When Using ADO
(Q190370)

<Quote>
CAUSE
By using the client-side cursors, the OLE DB provider is unable to requery the server for the updated record, and a "0" appears in place of the correct value. When you requery the recordset, the correct value appears.



RESOLUTION
Here are two ways to resolve this issue:

Use Server-side cursors (Set CursorLocation to "2" - adUseServer). This may incur a greater performance hit, as the client requeries the server for each record after each insert. NOTE: This solution is not valid if your provider does not support server-side cursors, for example, the MS REMOTE provider (RDS.)


Use the Resynch method of the ADO Data Control's underlying recordset or the ADO Data Control's own Refresh method. Call either of these methods from the DataGrid's AfterUpdate event. Performance may be improved if the Requery is performed after adding a batch of records. If the recordset is requeried after every insert, performance may be affected.

</Quote>

Also:
INFO: Identity and Auto-Increment Fields in ADO 2.1 and Beyond (Q233299)

INFO: Identity (AutoIncrement) Columns in ADO or RDS (Q195910)

Anthony

Author

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

;o)
CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Author

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

Author

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

Commented:
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

Author

Commented:
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 ....

Author

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

Commented:
...and a simple search-and-replace wont help you much?
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
CERTIFIED EXPERT
Top Expert 2012

Commented:
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
CERTIFIED EXPERT
Top Expert 2012

Commented:
emoreau,

The problem is that WolfgangKoenig is using ADO 2.0

Anthony

Commented:
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 !!!!!! (:-)>
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
Please maintain:

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

Commented:
WolfgangKoenig:

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

Lunchy
Friendly Neighbourhood Community Support Moderator

Commented:
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

Author

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.