HOW TO FIND THE NEXT AUTONUMBER

[dabineri]

If one has a field in an access file that is an autonumber field, how does one, with VB6 DAO, find the next number to be used when the next record is created? It is not always the next numeric value after the previous one if some records have been deleted in the mean time. I need to be able to know this next number BEFORE actually creating the record.

I hope this questions makes sense and there is someone who knows how to do this.

Thanks for any advice. David Abineri

 

[macleod1021]

The only way to actually KNOW what the next number will be is to create the record. If you're needing the number to give back to the user then just create a dummy record. Then you can get the newly created ID and use an update statement instead of a insert.

 

[dabineri]

Thanks for the response, I was afraid that might be the answer.
David Abineri

 

[macleod1021]

It's not as bad as it sounds tho. Theres a method you can call when you add the recordset to the database that will give you the Identity field. I don't remember what it is atm, but I'll try to find some old code and get it for you. I got the info from MSDN a while back when I first started working with ADO.

 

[johnwm]

Look for @@Identity in SQL books on line, or read the article here:

http://www.kamath.com/tutorials/tut007_identity.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Sheco]

If it is sequential and no rows are ever deleted then you should be able to see the next number by selecting the current MAX and adding one... but if there are multiple clients hitting the DB at the same time they might all get the same answer.

 

[MichaelRed]

... plus ... be careful that the autonumber "type" is just sequential. random and guid won't work for any 'tricky' lookup the last one

mayhap this is obvious ... but ...



MichaelRed

 

[DrSimon]

David, just picked this up. Following up from MichaelRed's comment, if you care about the value of the autonumber field, you probably shouldn't be using one! It would be better to have a function which creates the next value together with a locking facility if the table is shared.