Get next Id from a table in MS Access

Hi guys,

I want to get the next id (primary key, autonumber) of table without using max(id). beacuse max(id) wont give the next id if the table contains no rows. i think some system table stores the next sequence of all auto number fields of tables. (in sqlite there is sequence table which holds the next id of all primary key(autoicrement) fileds). Any one know how to get the next id from system table in MS Access(2003, 2007).

Who is Participating?
lee555J5Connect With a Mentor Commented:
I do not think that will work, and I do not think it is possible.
@peter57r This solution will only work if the last records added to the table still exist. Well, even then, you would have to add 1 to it to get the "next" ID which is what the OP wants. The problem enters if the last record (the one with the highest ID) is deleted. Now, the next ID is the current highest + 2. The problem gets worse if multiple last-added records are deleted. You have no idea what the next ID will be, and you cannot calculate it.
@angelIII OP wants the "next" ID, not the most recent
I looked through the Access system tables and do not see this information anywhere. ajones here says it is in a system table but does not say where. I have searched through the system tables adding records to my test table but cannot find this information. It may be stored in the test table but inaccessible.
You may want to create you own autonumber field you can control as here
you just use ..
nz(dmax("ID", "tablename"),0)
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
why not make the ID columns auto_increment, and fetch the ID after the insert?
with SELECT @@IDENTITY, you can :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.