7

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?

Improve this question
3
  • 2
    I think you don't really need to know the number beforehand. Why do you think you do? Commented Jun 28, 2011 at 21:56
  • 4
    I find it interesting how some people prefer to ask why you would need something rather than to just answer the question. Commented Jun 29, 2011 at 11:19
  • 2
    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! Commented Jul 1, 2011 at 0:52

3 Answers 3

Reset to default
11

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

What is CurrentProject.Connection ??
1

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.

Improve this answer

Comments

1

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.

Improve this answer

1 Comment

That's what I ended up doing.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.