Using SELECT LAST() with pyodbc and MSACCESS sometimes returns same value
Question: I have a strange problem that Im having trouble both duplicating and solving.

Im using the pyodbc library in Python to access a MS Access 2007 database. The
script is basically just importing a csv file into Access plus a few other
tricks.

I am trying to first save a 'Gift Header' - then get the auto-incrmented id
(GiftRef) that it is saved with - and use this value to save 1 or more
associated 'Gift Details'.

Everything works exactly as it should - 90% of the time. The other 10% of the
time Access seems to get stuck and repeatedly returns the same value for
cur.execute("select last(GiftRef) from tblGiftHeader").

Once it gets stuck it returns this value for the duration of the script. It
does not happen while processing a specific entry or at any specific time in
the execution - it seems to happen completely at random.

Also I know that it is returning the wrong value - in other words the Gift
Headers **are** being saved - and are being given new, unique ID's - but for
whatever reason that value is not being returned correctly when called.

    
    
    SQL = "insert into tblGiftHeader (PersonID, GiftDate, Initials, Total) VALUES "+ str(header_vals) + ""
    cur.execute(SQL)
    
    gift_ref = [s[0] for s in cur.execute("select last(GiftRef) from tblGiftHeader")][0]
    
    cur.commit()
    

Any thoughts or insights would be appreciated.


Answer: In Access SQL the `LAST()` function does not _necessarily_ return the most
recently created AutoNumber value. (See [here](http://office.microsoft.com/en-
ca/access-help/first-last-functions-HP001032232.aspx) for details.)

What you want is to do a `SELECT @@IDENTITY` immediately after you commit your
INSERT, like this:

    
    
    import pyodbc
    cnxn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Users\\Public\\Database1.accdb;')
    cursor = cnxn.cursor()
    cursor.execute("INSERT INTO Clients (FirstName, LastName) VALUES (?, ?)", ['Mister', 'Gumby'])
    cursor.commit()
    cursor.execute("SELECT @@IDENTITY AS ID")
    row = cursor.fetchone()
    print row.ID
    cnxn.close()
    

