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).

Thanks
LVL 1
prvijeshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
you just use ..
nz(dmax("ID", "tablename"),0)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why not make the ID columns auto_increment, and fetch the ID after the insert?
with SELECT @@IDENTITY, you can :)
http://support.microsoft.com/kb/232144
0
lee555J5Commented:
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 http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_25196772.html 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 http://www.tutcity.com/view/find-next-incremental-value-for-an-autonumber-field.18476.html.
Lee
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.