Are there issues with tables using an autonumber as a primary key in a back-end ms access db?

Question

I inherited an MS Access database at my office that is heavily used by several people over the network. This causes many issues with data collisions and locks. I want to split the db so that each user has thier own front-end app and maintain the core data on the server.

Several of the tables use an autonumber:sequence:long as thier primary key - in researching how to perform the split I've come across several posts that hint this can cause issues when distributing a database but I haven't been able to find anything solid. The issue seems to be that a user can begin a new record and receive the next autonumber but a second user can create a new record within a short interval and receive the same autonumber resulting in an error?

Does Jet handle this correctly or are there autonumber issues with a FE/BE database? If it's an unlikely-but-possile occurance I'm sure it will still be much better than what my users are currently experiencing but I'd like to know if there are ways I can minimize such issues.

Thanks for your help!

Answer 1

I had the same problem, nevertheless i did a workarround to get the autonumbering work from an Onload() Event

What I did is :

1. I create a recordset based on Your_Table everytime the user needs an autonumber
2. Open the recordset (rst)
3. Search if:
   -Your_Table is Empty, then assigns the value "1" to Your_field
   -Your_Table is has data without missing numbers,then assigns the value = "Count of lines + 1" to Your_field (1,2,....,n+1)
   -Your_Table has missing data (1,3,4,5,7) [Note "#2 and #7 are missing]", then uses a function to search in Your_Table the missing fields and assign to Your_Field the first missing value (#2 in this example)

Private Sub Autonumbering(Your_Table As String)
Dim rst As DAO.Recordset
Dim db As Database

On Error GoTo ErrorHandler

Application.Echo False

Set db = CurrentDb
Set rst = db.OpenRecordset(Your_Table, dbOpenDynaset)

                    With rst
                        .AddNew
                            'Your_Table is Empty, **then** assigns the value "1" to Your_field
                            If DMin("[Your_Field]", Your_Table) = 1 Then
                                'Your_Table is has data without missing numbers,**then** assigns the value = "Count of lines + 1" to Your_field (1,2,....,n+1)
                                If DMax("[Your_Field]", Your_Table) = .RecordCount Then
                                    'Assings n+1 value to [Your_Field] records
                                    Value = .RecordCount + 1
                                        ![Your_Field] = Valor
                                Else
                                    'Your_Table has missing data (1,3,4,5,7) [Note "#2 and #7 are missing]", **then** uses a function to search in Your_Table & _
                     the missing fields and assign to Your_Field the first missing value (#2 in this example)
                                    Value = MyFunction$(Your_Table, "Your_Field")
                                        ![Your_Field] = Value
                                End If
                            Else
                            'Agrega el número 1
                            Value = 1
                            ![Your_Field] = Value
                            End If
                        .Update
                        .Bookmark = .LastModified
                        Me.Requery
                        DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, Value
                        .Move 0, .LastModified
                    End With
ErrorCorregido:
Application.Echo True
Exit Sub

ErrorHandler:
MsgBox "An error ocurred, please verify numbering", vbCritical + vbOKOnly
Resume ErrorCorregido

End Sub

Here is the function that i found to get the missing values on an specific table, i cant find it anymore, but thanks for the one who made it.

Function MyFunction$(cstrTable As String, cstrField As String)

' Read table/query sequentially to record all missing IDs.
' Fill a ListBox to display to found IDs.
' A reference to Microsoft DAO must be present.

  Dim dbs     As DAO.Database
  Dim rst     As DAO.Recordset
  Dim lst     As ListBox
  Dim Col     As Collection

  Dim strSQL  As String
  Dim strList As String
  Dim lngLast As Long
  Dim lngNext As Long
  Dim lngMiss As Long

  ' Build SQL string which sorts the ID field.
  strSQL = "Select " & cstrField & "" _
   & " From " & cstrTable & " Order By 1;"

  Set Col = Nothing
  ' Control to fill with missing numbers.
  'Set lst = Me!lstMissing

  ' Collection to hold the missing IDs.
  Set Col = New Collection

  '// Vacía la colección
  'Erase Col
    ' Read the table.
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSQL)

  If rst.RecordCount = 0 Then
    ' The recordset is empty.
    ' Nothing to do.
  Else
    ' Read and save the ID of the first record.
    lngLast = rst(cstrField).value
    rst.MoveNext
    ' Loop from the second record through the recordset
    ' while reading each ID.
    While rst.EOF = False
      lngNext = rst(cstrField).value
      ' For each ID, fill the collection with the
      ' missing IDs between the last ID and this ID.
      For lngMiss = lngLast + 1 To lngNext - 1
        Col.Add (lngMiss)
      Next
      ' Save the last read ID and move on.
      lngLast = lngNext
      rst.MoveNext
    Wend
    ' Finally, add the next possible ID to use.
    Col.Add (lngLast + 1)
  End If
  rst.Close

  For lngMiss = 1 To Col.Count
    ' Build the value list for the ListBox.
    If Len(strList) > 0 Then
      ' Append separator.
      strList = strList & ";"
    End If
    ' Append next item from the collection.
    strList = strList & Col(lngMiss)
    ' For debugging only. May be removed.
    Debug.Print Col(lngMiss)
  Next
  ' Pass the value list to the ListBox.
  ' Doing so will requery it too.
  ' lst.RowSource = strList
  ' For debugging only. May be removed.
  ' Debug.Print strList
  MyFunction$ = Col(1)
  ' Clean up.
  Set rst = Nothing
  Set dbs = Nothing
  Set Col = Nothing
  Set lst = Nothing

End Function

Answer 2

There seems to be some confusion on your part about the process of splitting. When you do so, you end up with multiple front ends, but the back end is still a single file. Thus, there's no difference at all for the data tables in terms of Autonumbers from what you had before you split the application.

Answer 3

As long as you are not going for data replication (ie multiple subscriber databases, where users can insert new records in same tables but in different locations), you will not have problems with autonumbers as primary keys.

If you think that one of these days you might need to go for replication (different locations, one central database), do not hesitate to switch to unique identifiers (replication IDs).

Answer 4

I've had the misfortune of working with many Access databases in my youth. While there are many issues with Access, I do not know if I've ever run into a problem with AutoNumber columns in a split database, multi-user environment. It should work fine. This is such a common setup that there would be posts all over the Internet about it if were an issue.