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?