Retrieve next AutoNumber for Access table

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/开发者_如何学CJET using ADO, DAO, VB6 or any other available tools?

---

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.

---

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.

---

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.