asked on

Getting the value of an auto-inc-field (ACCESS, MS-SQL)

Hi,

by using sql 'insert' I add records to a table (access 2002, ms-sql and mysql). One of the fields is an auto-inc.

My question:
How can I get to know the next auto-inc which will be used ?
Alternatively:
How can I get to know the auto-inc-value of the last insert ?

For MySQL a function last_insert_id seems to exist but what to do with access and ms-sql ?

Thanks
K.-P. Becker

SOLUTION

geobul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mike Littlewood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

geobul

IMHO 'select max' is not a good solution for getting the last inserted identity value in multi-user environment.

>How can I get to know the next auto-inc which will be used ?
Using 'select max + 1' will return the next "possible" database-wide value not session-wide (speaking about multi-user environment again). I don't think that it's always guaranteed. The actual value might be greater in some circumstances.

Regards, Geo

KPBecker

ASKER

Thanks to the experts !

The answer of Geo is what I hoped would exist for all three DBs. It would be the cleanest way.

I think I have to use mikelittlewood's workaround in spite of the difficulty Geo pointed to.

I will split the points to both of you.

KPBecker

Mike Littlewood

I agree with you geobul for a multi user environment.