Where Does the Next AutoNumber Value Come From RRS feed

  • Question

  • How does Microsoft Access determine the next AutoNumber value when using start value plus increment.  

    Is there a register somewhere in the guts(i.e. the machine code) of Access that stores the last number used?

    How does Access deal with more than one user inserting rows into a table.  How does it know to give the second person a different number?

    Brad C. Parris Sr. IT Manager Custom Solutions

    Thursday, January 5, 2017 10:20 PM


All replies

  • Have you read the UA article on the subject found at:

    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: MS Access Tips and Code Samples:

    • Marked as answer by parrib1 Friday, January 6, 2017 3:30 PM
    Friday, January 6, 2017 2:03 AM
  • The internal number is part of the table definition (since each table (can) has it own auto number). Access  It places a lock on the table def when a record is added.

    Well remember that auto number can be skipped. If a user enters a new record and then decides to bail out (not save) by using ESC key or hitting undo, then the record (and that auto number) is discarded. So you often see gaps in the auto umber.

    So such numbers are “abstract” numbers for internal use, and as above shows they are often discarded.

    If you empty a table, and then do a compact + repair, the auto number is re-set.

    And while not recommend, you can set the next auto number with this:

    Sub SetNextID()
       Dim strTable      As String
       Dim strSql1       As String
       Dim strSql2       As String
       Dim ibuf          As String
       Dim nextvalue     As Long
       strTable = InputBox("What table to modify")
       If strTable = "" Then Exit Sub
       strSql1 = "INSERT INTO " & strTable & " (ID) SELECT TOP 1 "
       strSql2 = "DELETE ID FROM " & strTable & " WHERE ID = "
       ibuf = InputBox("Enter next value (blank enter will exit)")
       If ibuf <> "" Then
          nextvalue = ibuf - 1
          CurrentDb.Execute strSql1 & nextvalue & " AS Expr1 from " & strTable
           now delete this guy
          CurrentDb.Execute strSql2 & nextvalue
       End If
    End Sub

    So you could paste above into a module and hit F5 to run. However, you really should not need to do the above. (and the above assumes  you not ever entered a higher number then what you use in above (or a fresh C+R been done).

    You can also set the “increment” as to something other then 1, but again it really not an issue. Once just has to remember that Access will “lock” for a very short time while it grabs the number from the internal def and places it into that record.

    However since that record may not be saved, then such autonumbers are in a round about way can be often skipped and not used.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Friday, January 6, 2017 2:17 AM
  • @Deepak

    Could you allow Brad a day or 2 to actually read replies and post back.  You have to allow a little bit of time for the conversation to evolve, and/or the person asking the question to mark answers as they see fit rather than automatically marking them so that the question appears closed when in reality it isn't.

    Daniel Pineault, 2010-2016 Microsoft MVP Professional Support: MS Access Tips and Code Samples:

    Friday, January 6, 2017 1:27 PM
  • Thank you for your prompt and helpful answer. That was actually exactly what I was looking for.

    Brad C. Parris Sr. IT Manager Custom Solutions

    Friday, January 6, 2017 3:31 PM