what is the equivalent of LAST_INSERT_ID() in mx.ODBC ?

[Tom Harris]

I have seen this done by having an extra column in the table that is
defaulted to the current time, then querying the table (after inserting a
new row) for the row with the latest time. Obviously this will fail for
multiple users, and probably in other cases, but it will work in certain
situations. 

Alternatively if you can guarantee that your ID increases with each
insertion, you can just return the row with the highest ID, eg

SELECT TOP 1 * FROM tbl
ORDER BY id DESC

Tom Harris, Software Engineer
Optiscan Imaging, 15-17 Normanby Rd, Notting Hill, Melbourne, Vic 3168,
Australia
email tomh at optiscan.com     ph +61 3 9538 3333  fax +61 3 9562 7742

This email may contain confidential information. If you have received this
email in error, please delete it immediately,and inform us of the mistake by
return email. Any form of reproduction, or further dissemination of this
email is strictly prohibited. Also, please note that opinions expressed in
this email are those of the author, and are not necessarily those of
Optiscan Pty Ltd.




-----Original Message-----
From: Max M [mailto:maxm at normik.dk]
Sent: Friday, 21 December 2001 2:44
To: python-list at python.org
Subject: what is the equivalent of LAST_INSERT_ID() in mx.ODBC ?


I am trying out mx.ODBC in windows (connecting to an Acces DB), and I am
doing an insert where the key is auto-generated by access. Now how do I get
the key back?

I have tried "c.execute('SELECT LAST_INSERT_ID()')"

but gets a:

ProgrammingError: ('37000', -3102, "[Microsoft][ODBC Microsoft Access
Driver] Undefined function 'last_insert_id' in expression.", 4523)

So I guess that is not the right way.

Has anybody got an idea?  Is it an odbc, mx.ODBC or simply an access problem
to find the right documentation??

I have searched the web, the db.api, newgroups etc. but nothing is showing
up.

regards Max M