MS-Access – Retrieve the next autonumber of Access table

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

I have a table in Microsoft Access/JET in which there is an automatically set field, which is set incrementally and used as the primary key of the table. I need to know the value of the primary key for the next insert What is the record, but I need to know the value before inserting the record. Use SELECT MAX([ID])1 FROM [TableName]; because records are usually deleted from the end of the table, so it doesn’t work. (Insert new records just to find Out-of-value is not an option either.)

I know this can be done easily in MySQL by using the SHOW TABLE STATUS command. Is there anything that allows me to use ADO, DAO, VB6 or whatever Other available tools do the same for Access/JET?

You can use ADOX (Microsoft ADO Extensions for DDL and Security) to determine the current “seed” value of the autonumber field.

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 that this method may give wrong results in multi-user situations… if another user can be between your retrieval of the next automatic number and the actual execution of the INSERT INSERT secretly. If it is important, you can verify that you got the expected value by checking SELECT @@ Identity after INSERT.