How to retrieve value of Autoinc column in Access, after INSERT
lazarus / 16-Databases

----------------------------------------------------------------------
grandehombre · 2013-07-13 08:00

I am using Lazarus v10.0.10,  FPC 2.6.2, MSAccess2010 and win7-64 and
I am trying to get the ID of a newly-added record, without any luck.

The ID is an auto-increment field.

Here is a sample of how I try to do it:
Code: [Select]  qryJobs.Insert;
        qryJobs.FieldByName('myDataSource').AsString := jobtype;
        qryJobs.Post;
        qryJobs.ApplyUpdates;

// I was expecting to see the ID of the newly added record here but I
get a 0 instead.
        newID := qryJobs.FieldByName('ID').AsInteger;

Any suggestions would be greatly appreciated!

----------------------------------------------------------------------
BigChimp · 2013-07-13 08:59

Perhaps something like getting the result from
Code: [Select]SELECT @@Identity
see
http://www.utteraccess.com/forum/Add-Record-VBA-SQL-retu-t1538936.html

Haven't tested it.

----------------------------------------------------------------------
grandehombre · 2013-07-13 09:16

Thanks BigChimp.

I'd seen a few references to @@IDENTITY but felt a little unsure about
it.
Seeing your post finally made me jump in and try it.
It works.

The only thing on my mind is what happens in a multi-user environment
when multiple users are adding records?
Would this return my last added record or simply the last added by
anyone?

The thing that wrong-footed me in all this, is that I expected to be
able to read back the record from the db and get the new ID value.

----------------------------------------------------------------------
BigChimp · 2013-07-13 09:26

I'd hope it would be your primary key

More seriously: you have your own connection+transaction that you use
against Access; I'd assume Access is smart enough to pass you your own
pk. Perhaps the Access docs can shed some light.
Edit: link to interesting stackoverflow thread:
http://stackoverflow.com/questions/4135437/get-last-insert-id-from-
access


Also, perhaps newer versions of Access support INSERT...RETURNING like
syntax - no idea, my latest version is Access 2002/XP.

As for reading back the value from the record: you could, using
another select if you have a sufficiently unique combination of fields
(not being the PK) that identify the records... but that's hardly as
elegant as the @@IDENTITY solution.

----------------------------------------------------------------------
grandehombre · 2013-07-13 13:42

Yeap, ID is indeed the primary key :-)

The best I can get from docs I've seen online is that
QuoteThe Jet OLE DB version 4.0 provider supports the SELECT
@@Identity query that allows you to retrieve the value of the auto-
increment field that is generated on your connection... and I can only
assume/hope that, if someone else added a record between my INSERT and
this SELECT, I don't get to see his record!

Thanks again

----------------------------------------------------------------------
BigChimp · 2013-07-13 14:14

Quote from: grandehombre on July 13, 2013, 03:42:22 pmThe best I can
get from docs I've seen online is that
QuoteThe Jet OLE DB version 4.0 provider supports the SELECT
@@Identity query that allows you to retrieve the value of the auto-
increment field that is generated on your connection... and I can only
assume/hope that, if someone else added a record between my INSERT and
this SELECT, I don't get to see his record!
Hmmm yes - that is enough confirmation: because somebody else would be
using a *different* connection