Last inserted ID in MS access

Hi. I am developing a site for a gentleman and have to use access as the DBMS. I need to insert some data into the database. Usually when I have one main table (e.g., Users) and need to also insert data into some dependant tables (e.g., Orders) where the ID from Users is a foriegn key of the dependant table, I use the mysql function mysql_insert_id to get the last inserted ID.

I was wondering if anyone had an equivelent way to obtain the last inserted ID from a Jet Access DB? I could just insert it and turn around and do a select getting the largest ID, but that is risky if another gets inserted by another user. Any ideas?

Thanks a lot.

^^bump

Don't do that bump crap around here.

If no one is answering your question, then no one knows the answer.

Can you lock the tables in Access? lock the table, insert, select max(id), release lock.

Maybe check out this site for any info:

http://www.access-programmers.co.uk/

---John Holmes...

Thanks for the reply.

The reason I bumped it was because I know this forum moves very quickly, as I have contributed to it in the past. Considering that others probably have more of a life than you must and don't hover over these forums every hour of the day, I thought I would make it visible to the ones that might be able to look at it in the daytime rather than at night, which is when I originally posted it.

You seem awfully arrogant that if you don't know the answer, then no one does. My guess is that there are some knowledgable people that frequent this forum that aren't on even every day. Something to think about.

Anyway, thanks for your reply. I will check out your link.

John has every right to do what he did, he is the moderator. And he is right, if no one answers you then no one knows the answer.

This really isn't the area to post a question like that anyhow, these forums are for mysql and php, not access/jet. You would probably get a better answer on forums dedicated to those databases.

All I made is a simple request. If you do frequent these boards enough, you'll know that questions get answered if people know the answer.

Since this is a Access question and not PHP, I'm moving this to the General Database forum. I guess you could be using PHP and ODBC to access the database, but since there is no equivilent to mysql_insert_id() in the ODBC functions, you'll have to find an Access solution.

Check out this, too

http://www.php.net/manual/ro/faq.databases.php#AEN78840

it talks about some alternative ways to use Access and PHP.

---John Holmes...

1. Start a transaction by disabling Autocommit, with odbc_autocommit(false) or odbc_autocommit(0).

2. "SELECT MAX(id) etc..." or whatever your primary key column is, so you get the most recently used ID.

3. Increment that number by one, and use it to explicitly set your next id for insertion.

4. Run your insert query using that incremented value. If the INSERT query fails, then do an odbc_rollback(). If it succeeds, then do odbc_commit.

Now you have inserted the record, and you have the insert ID from part 3 above. Since we used a transaction, there is very little chance for another user to "sneak in" and insert another record with the same ID, but if so, the insert will fail, so you can just do a rollback, and increment the number one more time, and try again.

Enclose your whole series of related INSERTs in this transaction, so that you don't accidentally insert a related record with an id of 0.

that's after establishing a connection with odbc

and the ,1 is the personID assuming its the first field or alternatively putting the name of the field there

eg.

Stay away from select max.

Access supports the syntax

select @@identity

That query can be used after an insert statement to get the latest counter value.