Return autonumber value of newly inserted record? 2

[jonscott8]

I'm INSERTing a record into an Access database that uses the AutoNumber datatype for its primary key field. Table definition is as follows:

Table: RefHdr
Fields: RefHdrKey AutoNumber
AuthNo Char(13)
Comment Char(30)

I do a: INSERT INTO RefHdr (AuthNo,Comment) VALUES ('3001','Referred to Dr. X')

After doing the INSERT, how can I determine what Access assigned to the RefHdrKey field value, baring in mind that this is a multi-user system with new records being added every few seconds?

FWIW, I'm using SPT from a VFP front-end.

Thanks In Advance.

Jon Jon Hawkins
jonscott8@yahoo.com

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[DougP]

Couple of things to try.
Add a record then immediately save it which will generate a number.
Or use the Max function to get the last number issued and then add one to it.

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[jonscott8]

Or use the Max function to get the last number issued and then add one to it.

That would work 98% of the time, but because we're talking nanno-seconds...if after I query SELECT MAX(), another user inserts a new record before my INSERT query executes, there is no guarantee that my returned value will still be accurate after my INSERT query.

FWIW, the answer to this riddle is with Jet 4.0, MS introduced support for the 'SELECT @@IDENTITY' that was being used in SQL Server.

For Additional Info:

http://support.microsoft.com/support/kb/articles/Q232/1/44.ASP

Jon Hawkins
jonscott8@yahoo.com

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed