Retrieve next AutoNumber for Access table

Question

I have a table in Microsoft Access/JET that has an AutoNumber field that's set incrementally which serves as the table's primary key. I need to know what the value of the primary key will be for the next inserted record, but I need to know the value before the record is inserted. Using SELECT MAX([ID]) + 1 FROM [TableName]; will not work because records are routinely deleted from the end of the table. (Inserting a new record just to figure out the value is not an option either.)

I know that this is easily done in MySQL by using the SHOW TABLE STATUS command. Is there anything that will let me do this exact same thing for Access/JET using ADO, DAO, VB6 or any other available tools?

I think you don't really need to know the number beforehand. Why do you think you do? — David-W-Fenton, Jun 28 '11 at 21:56
I find it interesting how some people prefer to ask why you would need something rather than to just answer the question. — HK1, Jun 29 '11 at 11:19
Why would anybody waste time answering a pointless question? That is, why should I explain how to do something that nobody should ever need to do in the first place? A surrogate PK value shouldn't be given any meaning, so you should never care what the next value would be. If you do care, then you should be managing the values in the field yourself, not relying on the database engine to assign them. And, of course, the answer on how to find out the seed value had already been posted by the time I posted my comment, so there was no reason for me to post an answer at all! — David-W-Fenton, Jul 01 '11 at 00:52

HansUp · Accepted Answer · 2015-12-02T16:18:00.963

You can use ADOX (Microsoft ADO Extensions for DDL and Security) to determine your autonumber field's current "Seed" value.

Public Function NextAutonumber(ByVal pTable As String, _
        ByVal pAutonumField As String) As Long

    Dim cat As Object
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = CurrentProject.Connection
    NextAutonumber = cat.Tables(pTable).Columns(pAutonumField).Properties("Seed")
    Set cat = Nothing
End Function

Note this approach could give the wrong result in a multi-user situation ... if another user can sneak an INSERT in between the time you retrieve the next autonumber and you actually do your INSERT. If it's critical, you could verify whether you got the value you expected by checking SELECT @@Identity after the INSERT.

What is **CurrentProject.Connection** ?? – Marco Sanchez Sep 21 '16 at 01:06 — Marco Sanchez, Sep 21 '16 at 01:06

HK1 · Answer 2 · 2011-06-28T11:23:07.170

The Autonumber feature in Access is very limited when compared to SQL Server's identity column or MySQL's AUTO_INCREMENT column. There is no way to retrieve the next available number short of using SQL which is inaccurate. As you stated, records deleted from then end will make it so that the top number and the next number do not match. (See Edit below).

I think you're better off using a long number field (not autonumber) and creating your own auto-incrementing function that you use when inserting new records.

Edit:
I see that HansUp has actually found a way to do what you were asking for. Please see his solution.

That's what I ended up doing. – Lance Roberts Jun 27 '11 at 21:11 — Lance Roberts, Jun 27 '11 at 21:11

JeffO · Answer 3 · 2011-06-27T22:05:42.547

I initially agreed with HK1's solution to do your own, but Hans has found the better solution.

I was going to suggest finding the ID by inserting a record in a trasaction with rollback. The problem is, you will get the next number (Ex: 62), but when you actually add the new record for real, you get the ID after that (63). Repairing will reset the values to the next available number.

Just curious, why do you need to know this number? If you have a record (paper or otherwise) that needs this value, you should enter it in that system/paperwork after you actually enter the record.

Retrieve next AutoNumber for Access table

3 Answers3

Linked