Reliable way of getting index for record added

[ian_w]

Hi,

Currently replacing our BE with SQL Server 2008 and accessing using Access 2010 FE.

Tables linked via ODBC and all database opens are done using DAO (was originally an Access 97 database).

Run into an annoying problem, would seem a lot of code retrieves the index of the current record being added for further use, now I am adding records in to SQL this isn't working.

The only was I can do this right now is something along the lines of

.AddNew
      !Field1
      !Field2
.Update
.MoveLast
MyVariable = !Index

Not a great way of doing things I know!

I know the answer is to rewrite using SQL Inserts or ADODB but this is further down the line, all tables are shared through multiple apps and each one needs to 'work' before we can go live with any of it :banghead:

Is there a more reliable way I can achieve this in the short term?

Thanks!

 

[SQL_Hell]

When you say the index of the current record, you mean the primary key that is identity (auto number)?

SQL works differently than access in the respect that identity value is only updated once the row is committed.

I am not sure if there's a better way than you mentioned hopefully someone can answer that.

But when it comes to changing over to ADO, you want to use a stored procedure to do the insert and use the scope_identity() function within that stored procedure to retrieve your latest primary key value.

 

[ian_w]

I converted the routines where I was needing the primary key to use ADODB and this is now working fine.

I just inspect the primary key value once the update has been posted.

Thanks