Re: get insert_id in odbc

At 21:28 19.02.2003, Jonathan Villa spoke out and said: --------------------[snip]-------------------- >Using MySQL, one can use mysql_insert_id to get the value of an >auto_increment column. > >How would I do this using ODBC and Access? --------------------[snip]-------------------- Assuming the ID column is named "ID": insert into TABLE (columns) values (values); select max(id) from TABLE; Caution 1: If your database supports it you MUST enclose both line within a transaction or you might get a wrong result when somebody inserts another row in TABLE before your select max(id) statement executes. Caution 2: On heavily filled tables, select max(id) might have some performance issues. Generally I despise using Access in a multiuser environment. Too slow, too error prone with simultaneous access. And I don't know if transaction support is more than syntactically implemented... Using PostgreSQL, for example, you'd have the ID column declared like this: create table TABLE ( id serial, ... ); This generates a sequence that can be queried without performance penalty: select currval('TABLE_id_seq'); -- >O Ernest E. Vogelsinger (\) ICQ #13394035 ^ http://www.vogelsinger.at/