Autonumber Fiels with Access 97 linked to SQL Server

[lachie]

I tried looking through old threads and couldn't come up with any information that was helpful..

Scenario - Converting our Access97 database to use linked tables to a SQL Server.

Problem - We have a table "blah" with autonumber field "num". The existing code is;

set rs = db.openrecordset("blah&quot
rs.addnew
recordid = rs!num

This works fine with the native access tables, but when using linked tables to the SQL Server we get a few errors - most of which i had expected. So the code changes to...


set rs = db.openrecordset("blah",dbopendynaset,dbseechanges)
^^^^^^^^^^^^^^^^^^^^^^^^^
rs.addnew
recordid = rs!num

the problem is that when using the native table, the autonumber field "num" is populated with the new id. When using the sql server linked tables, the field is not populated with the new autonumber.

I suspect that this has to do with native tables the jet engine assigns the autonumer wheras the sql engine takes over the job of doing this when using the linked tables.

Does anybody know of a workaround / have knowledge with this kind of stuff?

Thanks,

Lachlan.

 

[Aivars]

Hi Lachlan!

I posted my proposal about Autonumber many times before. I did advise: don't use Autonumber because when it's needed to data export/import or to work with ODBC data lot of problem would proceed. Change Autonumber type to Long or Integer.

rs.addnew
if dcount("recordid", "blah&quot=0 then
recordid =1
else
recordid = dmax("recordid", "blah&quot+1
endif

Aivars

 

[lachie]

Thanks for the info, but I think I've come up with a suitable workaround.

Creating an autonumber is going to cause far more grief than it will solve in a multi-user environment. (I've had experience with the locking that access / jet provides - not too crash hot).

Because the SQL Server engine assigns the autonumber after you have done an update, you can't retrieve this information until the update has been fully processed. This isn't so bad after all. To get the autonumber generated you have to open a recordset and then move to the last member of that recordset and retrieve the autonumber...

ie if we had a names table with an autonumber field nameid this code would look like


set rs = db.openrecordset(Names)
rs.addnew
'all the fields in here
rs.update
rs.movelast
theautonumfortherecordwejustadded = rs!nameid

So, if that helps any one else out there - give it a go.

L