ADO, AddNew, and ID Fields

[MelissaEvans]

Overview:
I have an ADO connection object and recordset object. I need to be able to add new records to the recordset object and so I am using the AddNew method. (My other option is to do it "behind its back" by executing a SQL insert statement; but I can only get a static cursor, so it doesn't see the new one. To get around that, I'd have to resync or close then open the recordset and as there are 10,000 records in my test table - 100,000 in the real one - so that is not a valid option.)

Problem:
The problem is the ID field (primary key) is not updated in my recorset, it always comes back as 0. Even looking at the UnderlyingValue property shows 0; but there really honestly is a valid key in the table itself. Without having the key, I cannot alter or delete the record (it complains that the key I have in the recordset has been deleted in the table). The primary key is an ID field that automatically
incriments itself.

Details:
I am connecting to a SQL server database, but my manager doesn't want to be tied to this forever - our customers would love to be able to use Access also. I am currently using the MSDASQL provider (ODBC) and Client side cursor (server side brakes other things) for the connection object. The recordset has a Static cursor type (I have requested KeySet and Dynamic, but always get Static) and the lock types I have tried are BatchOptimistic and Optimistic.

What I've Tried:
I *can* get the (assumed) new primary key (I look for the largest value in the table itself immediately after the AddNew). This isn't ideal as there may be multiple people adding at the same time that could cause problems. I tried to use the @@IDENTITY property, but it only returns 0 (and it's a global variable for the entire database, there are other tables that could be added to and change this value to something totally wrong - not to mentiong this is a SQL only solution). Even assuming I can get the correct key
each time (a leap of faith), I can't do anything with it. I've tried to assign the key value I retrieved to the key field in my recordset; but that field is not updatable.

Any ideas on how to get around this?

 

[petermeachem]

Join the club Melissa. I have the same difficulty with Access front and sql back. There has been a great long discussion on the MySql list about this same thing with all sorts of bizarre solutions. What they were talking about was adding some other unique key to the table and reading that back. Are you sure you need the autonumber at that time. I've told the customer that they can't see it until they go to another record and back and fortunately I'm not using it to linek to another table.

If you want I'll try to get the MySql info. Peter Meachem
peter@accuflight.com

 

[MelissaEvans]

I ended up switching to a server side cursor which doesn't have a problem (using a keyset cursor type, the data is always up to date). I tried using a copy of the ID field and accessing that, but it didn't do any good. If anyone else has this problem, good luck. *sigh*

 

[Terpsfan]

I have had this problem as well but I have corrected it. First I'm not understanding why you are connecting to SQL using ODBC. Try using the SQL OLEDB provider. If you go into control panel and then ODBC you will find out if you have a SQL Server OLEDB Provider. I think with that you'll get more options for your cursors. You can set your cursor type in code when you open your recordset object.
Dim objConn as ADODB.Connection
Dim objRec as ADODB.Recordset
Set objConn = New ADODB.Connection
Set objRec = New ADODB.Recordset
objConn.ConnectionString "Provider=SQLOLEDB.1;Data Source=Servername;Initial Catalog=Databasename;UID=sa;PWD="
objConn.Open
objRec.Open "Select ...",objConn, adOpenDynamic,adLockOptimistic,adCmdText


Now for updating your autonumber field try this:
On one of the control's Validation event
objRec.MovePrevious
objRec.MoveNext

This has worked for me

 

[chiph]

The trick to using the @@IDENTITY is to make sure that you don't close the ADO connection object after you do your Insert statement. Also, it has to be the very next statement you issue. So:

[tab]INSERT INTO MyTable SET blah='foo', bleh='bar'

[tab]Select @@IDENTITY as LastIdentity

And the LastIdentity column will contain the identity value that was generated. A big caveat is that I don't know if this statement works with Access -- anyone else know?

Chip H.

 

[MelissaEvans]

Please notice that I said I cannot assume SQL Server - my manager likes to sell Access as well (or Oracle or whatever the customer wants). That's why I have to use the ODBC provider and the @@IDENTITY will not work in my case. But like I said, I've managed to use a Server side cursor which has fixed that problem (and caused a few other different ones). Thanks for your input.