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?

Answer 1

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.

Answer 2

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.