Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003

    Unanswered: Access 2000 Duplicating Autonumber Primary Key

    I have a situation, I have a multiuser access 2000 database that has a table with an autonumber primary key field in it . When the users are entering information it is trying to duplicate previous autonumber entries
    and will not let them enter there data. does anyone know a workaround for this issue? Please Help Thank you in advance

  2. #2
    Join Date
    Jan 2003
    Rockville, MD
    Hi npavjrt,

    Here's a piece of code you can run on your affected database (either from another database, or from a module in the problem database). You have to have the Microsoft DAO Object Library selected as a reference for it to work though.

    Pass the subroutine the full drive/path/name of the database you want to correct, ex:

    ResetAllAutoNumberFields "C:\MyFolder\MyDatabase.mdb"

    If it's the "current" database, you can use:

    ResetAllAutoNumberFields CurrentDb.Name

    Public Sub ResetAllAutoNumberFields(varData As Variant)
       Dim dbSelectedMDB As DAO.Database
       Dim lngFieldIndex As Long
       Dim lngTableIndex As Long
       Dim strAutoNumberField As String
       Dim strTableName As String
       DoCmd.Hourglass True
       Set dbSelectedMDB = OpenDatabase(varData)
       ' look through every table in the mdb
       For lngTableIndex = 0 To dbSelectedMDB.TableDefs.Count - 1
          ' look through every field in each table
          For lngFieldIndex = 0 To dbSelectedMDB.TableDefs(lngTableIndex).Fields.Count - 1
             If dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Type = 4 Then
                Select Case dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Attributes
                Case 16, 17, 18
                   ' this is the autonumber field
                   strAutoNumberField = dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Name
                   strTableName = dbSelectedMDB.TableDefs(lngTableIndex).Name
                   dbSelectedMDB.Execute _
                      "INSERT INTO [" & strTableName & "] ( [" & strAutoNumberField & "] ) " & _
                      "SELECT TOP 1 [" & strTableName & "].[" & strAutoNumberField & "] AS ID " & _
                      "FROM [" & strTableName & "] " & _
                      "ORDER BY [" & strTableName & "].[" & strAutoNumberField & "] DESC;"
                   Exit For
                End Select
             End If
       Set dbSelectedMDB = Nothing
       DoCmd.Hourglass False
    End Sub
    I've found a cause of this problem (though I can't say it's the only possible cause):
    Someone was editing a record WHILE the database was copied or zipped.
    Last edited by JTRockville; 06-24-03 at 16:25.

  3. #3
    Join Date
    Jun 2003
    thank you very much it was helpful

  4. #4
    Join Date
    Jun 2003
    Have also had same problem occasionally on one large database with several tables containing >100k records. Thanks for the reply for code to sort it out. We found it was always compact of back end data tables which caused the autonumber to be reset to a number lower than a previously used number. Problem resolved by upsizing back end to SQL server. Also had corruption of autonumber field in multiuser environment when forms did not use record locking; record locking (lock current record) on forms solved it, except for when compacting, which never went away until we went to SQL server for back end. Now have db running well with >30 concurrent users on access front ends with no stability problems. By the way, all sorts of problems got MUCH worse (before migration to SQL server,) when front ends were running on terminal server!!!!! Be VERY cautious about using access front ends on terminal server, with access back end as well!!!!! Terminal server works good with back end in SQL server.

  5. #5
    Join Date
    May 2003
    Provided Answers: 5
    I have elimintaed this problem by using dao to addnew when someone opens the dataentry form
    Using the currentuser to populate the new record it is a simple open/close routine that is quick and efficient
    then the user is basically editing their record
    No lock conflicts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts