last inserted ID

[proforg]

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.

 

[araskas]

you can have a form/subform pair to solve your problem. You can find lot of examples on using form and subform in Microsoft Solutions sample database.

 

[proforg]

sorry, but that's not the solution.
I need to convert data from one DB to another - with slightly different sructure. It seems to me that forms help /subform wontin this case

 

[Jimbob]

Just an idea, but how about adding a Now() field to the table? That way all you have to do is find the record with the latest value in the Now() field and return the ID...

???


[This message has been edited by Jimbob (edited 03-28-2002).]

 

[Fornatian]

Why not store the LAST INSERTED ID into a public long tye variable each time time a record is created, then you can get the number any time you want it.

Another alternative is to store it in a one record table each time so you can retrieve it in a query without using a function.

The first option is the best as the variable will be stored locally and thus eradicate the risk of another record being entered in the mean time.