Return autonumber value of newly inserted record? 1

[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 posted this question in the Access forums, but since I am using a VFP front-end, figured I'd post it here too.

Thanks In Advance.

Jon Jon Hawkins
jonscott8@yahoo.com

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

 

[foxdev]

Jon, I'm not an Access user, but FWIW: in SQLS, you query the @@IDENTITY global variable (i.e., sqlexec(nHandle, "select @@identity&quot).


Robert Bradley

http://www.foxdev.com/

 

[jonscott8]

Thanks Robert.

I had read numerous KB articles stating the Jet 4.0 OLE DB Provider supported the 'SELECT @@IDENTITY' functionality, but none of the articles came remotely close to implying it worked with Jet ODBC.

So after your post I thought,..."what the hell..give it a try." and whattya know...It worked.

Any thoughts on how to do the same thing with the SERIAL data type in Informix??? Jon Hawkins
jonscott8@yahoo.com

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

 

[foxdev]

Sorry, no Informix experience...but I know an Informixer that I can ask if you are still stuck.

Robert Bradley

http://www.foxdev.com/

 

[jonscott8]

After consulting several Informix User Groups, I found that it isnt possible.

The way I was told I would have to do it, is by inserting a Candidate key field and after the INSERT, doing a query on the Candidate key.

But if your "Informixer" connection knows a better way, I'd surely be open to his/her suggestions. Jon Hawkins
jonscott8@yahoo.com

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