greyhawk has asked for the wisdom of the Perl Monks concerning the following question:

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.

Replies are listed 'Best First'.
Re: Last Insert ID in MS Access
by ahmad (Hermit) on Jul 16, 2010 at 20:21 UTC

    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

Re: Last Insert ID in MS Access
by sflitman (Hermit) on Jul 17, 2010 at 07:03 UTC
    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

      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.

        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.

Re: Last Insert ID in MS Access
by bobf (Monsignor) on Jul 17, 2010 at 04:28 UTC

    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.

      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.

Re: Last Insert ID in MS Access (example using @@IDENTITY)
by bobf (Monsignor) on Jul 24, 2010 at 07:04 UTC

    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.

Re: Last Insert ID in MS Access
by Anonymous Monk on Jul 19, 2010 at 08:50 UTC
    ...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?

      $idSQL = 'SELECT @@IDENTITY AS lastID'; $stmtID = $conn->prepare($idSQL); $stmtID->execute(); $row = $stmtID->fetch(); echo "Last insert ID is: " . $row["lastID"];
Re: Last Insert ID in MS Access
by GotToBTru (Prior) on Nov 19, 2015 at 19:35 UTC

    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.

    Dum Spiro Spero