Last Insert ID in MS Access

[greyhawk]

I am using Perl (DBI, ODBC) to update an MS Access database. Works OK but I need to be able to get the (autogenerated) ID of the last record inserted and I can't seem to find any way to do it.

[ahmad]

I usually use this line $dbh->last_insert_id(undef,undef,undef,undef); but I only work with MySQL haven't tested it with other database driver

[sflitman]

You may be able to run

SELECT @@IDENTITY FROM MyTable;

and pass that through to the underlying table. I found it by googling for MS Access and last insert id. One would think the DBD::ODBC documentation should help too, but I found it a bit opaque on this topic. In many advanced SQL dialects you can also say something like

INSERT INTO MyTable (x,y) VALUES (1,2) RETURNING rowid;

Then retrieve the rowid by fetching from the statement handle, if the DBI driver permits this.

HTH,
SSF

[james2vegas]

This article suggests that the correct syntax is SELECT @@IDENTITY, and that you should open a second connection to get it. This is presumably problematic with multiple users, so it is as effective (and unreliable) as MAX(ID) would be. I think you could probably lock the table for update and unlock the table after getting the last id.

[mje]

I think you'll find it doesn't work if run in a "second connection". I think you just need to ensure it is run as a new statement in the same connection after any outstanding commit.

[bobf]

Unfortunately, last_insert_rowid is not supported by the ODBC driver. When I need to do this I query for what I just inserted and get the ID that way.

I hope another monk can prove me wrong, as this approach is a bit cumbersome.

[mje]

As you correctly say, last_insert_id is not supported by DBD::ODBC. The main reason for this is that there is no ODBC way to retrieve the last inserted ID. Each database and ODBC driver /may/ provide a way of obtaining it but support varies massively between databases and drivers (select @@identity, select myseq.currval from dual, insert ... returning, etc)

As others have pointed out there may be ways of obtaining the last inserted id with MS Access but it rather depends on the version of access you are using and access method. This highlights again, why DBD::ODBC does not do it.

If people want to send me SQL or other methods that work for different ODBC drivers and databases I will happily add them to the DBD::ODBC pod.

[bobf]

The following example works for me. Applicable versions are shown inline:

use strict;
use warnings;

use DBI;

#MS Access 2003
#DBI->installed_versions;
#  Perl            : 5.008008    (MSWin32-x86-multi-thread)
#  OS              : MSWin32     (5.0)
#  DBI             : 1.59
#  DBD::ODBC       : 1.13

# Connect to the db

my $dbpathfile = 'C:\testdb.mdb';
my $db_driver = 'Microsoft Access Driver (*.mdb)';
my $dsn = "DBI:ODBC:DRIVER=$db_driver;DBQ=$dbpathfile";

my $dbh = DBI->connect( $dsn, '', '', { RaiseError => 1, AutoCommit =>
+ 1 } )
            or die $DBI::errstr;

# Create table for test data

my $tblname = 'test_table';

my $tbl_sql = join( ' ', 'CREATE TABLE', $tblname,
                         '(ID AUTOINCREMENT, field1 TEXT,',
                         'CONSTRAINT', $tblname . '_pk', 'PRIMARY KEY 
+(ID))' );
$dbh->do( $tbl_sql );

# Insert test data

my $insert_sql = join( ' ', 'INSERT INTO', $tblname, '( field1 ) VALUE
+S ( ? )' );
my $sth = $dbh->prepare( $insert_sql );
$sth->execute( 'test data' );

# Use @@IDENTITY to get the last insert ID
# See http://support.microsoft.com/kb/815629

my $id_sql = 'SELECT @@IDENTITY';
$sth = $dbh->prepare( $id_sql );
$sth->execute;
my $id = $sth->fetchrow_array;
print "Last insert ID is: $id\n";

Thanks to james2vegas and his post for seeding this approach. Thanks also to mje for considering adding this example to the docs for DBD::ODBC.

[Anonymous Monk]

...but I need to be able to get the (autogenerated) ID of the last record inserted and I can't seem to find any way to do it.

Do you really? Why?

[Anonymous Monk]

$idSQL = 'SELECT @@IDENTITY AS lastID';
$stmtID = $conn->prepare($idSQL);
$stmtID->execute();
$row = $stmtID->fetch();
echo "Last insert ID is: " . $row["lastID"];

[GotToBTru]

If your process is the only one inserting records, another approach would be to find the previous maximum for the session (SELECT max(id)...), and within your process increment it and include it in the insert statement instead of having the db autoassign.