Strange AutoNumber Key Behavior 2007-2010

[Katie Mitchell]

I have experienced this same problem after moving to Access 2010 from 2003. After compact and repair, the autonumber field would get reseeded to an already used number, making additions to the table impossible because the autonumber field is set to unique values only.
The autonumber field is not the primary key in my table. If I experimentally made the autonumber field the primary key, the field behaved normally after compact and repair. I think this is probably a bug in Access 2010.
Did you find a permanent fix for your problem? My understanding and experience is that even when you reseed the autonumber to an appropriate number, it gets reseeded inappropriately by compact and repair. Since compact and repair needs to be performed regularly on an Access database, this is a serious problem.
To fix my problem, I am going to change my autonumber to a long integer data type, and make the forms that insert records into my table increment the value.

 

[ss8880]

I have the same problem in access 2010 with now 15000 records that linked to the id in between as a tree ( i cannot make repairs)
any help plz.....

 

[ss8880]

Microsoft Access Tip: Creating an AutoNumber Field Starting with a Number Greater than 1
In Microsoft Access tables, the AutoNumber field type allows you to assign a unique sequential number to each row in a table. AutoNumber fields are often used as the Primary Key, since they are unique and permanent (i.e. the value cannot be changed once it is assigned).
For new tables, AutoNumbering begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1.
To do this, create an Append query to insert the value of the ID one less than your desired starting number (or Seed value). For instance, assume that you want to start with the Seed value 1000.
First, run this query:
INSERT INTO tblName ( ID )
SELECT 999 AS Expr1
Where tblName is the table name, and ID is the name of the AutoNumber field.
Then, run a second query to delete the row that you inserted:
DELETE
FROM tblName
WHERE ID=999;
(Alternatively, you can simply open the table and manually delete the row that you inserted.)
Now, the next record added to the table is assigned the value 1000.
This method works with a new table, or with a table whose AutoNumber has not yet reached the Seed value. (Of course, the ID field for existing rows will not be changed, but subsequent rows will start with 1000.)

 

[Katie Mitchell]

ss8880, did you fix your problem with the queries you ran, or do you still have a problem?

 

[Pat Hartman]

Is this a problem because you installed the 64-bit version of Office 2010? The 32-bit and 64-bit versions of Office are incompatable and installing both A2007 (32-bit) and A2010 (64-bit) may be what is making the seeding break.

There is no reason to install the 64-bit version of Office just because you are using 64-bit hardware and 64-bit Windows. In fact MS is still recommending the installation of 32-bit Office UNLESS you SPECIFICALLY need HUGE spreadsheets. There is absolutely no gain with installing 64-bit Office and certainly a lot of pain. In addition to the coding problem you will have with APIs you've used, ActiveX controls like TreeView will also not work nor will any of your normal add-ins.

Uninstall the 64-bit version of Office and reinstall with the 32-bit version. Once you fix the autonumber seeds, they should stay fixed whether you use Access 2007 or A2010 (32-bit).

 

[techexpressinc]

Re: AutoNumber 2007-2010 - duplicate key

We have had multiple times when we are getting a duplicate key message and no users can enter new rows.

Looking at the table there is not a duplicate record.

The only table connected has a field as a primary key, autonumber field type, and index (no dups).

If I delete all the rows and reload the table, the users can add rows again.

We are running MS-2010 Access, with a AccessDB formatted as 2003. Is this a MS bug?

Russ

 

[Pat Hartman]

Yes. Sometimes Jet/ACE looses the current seed value for a table. The two links in post #2 earlier in the thread tell you how to fix the seed so it starts generating numbers in the correct range.

 

[Khalid_Afridi]

Is this a problem because you installed the 64-bit version of Office 2010?
There is no reason to install the 64-bit version of Office just because you are using 64-bit hardware and 64-bit Windows........

Uninstall the 64-bit version of Office and reinstall with the 32-bit version. Once you fix the autonumber seeds, they should stay fixed whether you use Access 2007 or A2010 (32-bit).

Our company have more then 10,000 pcs and they change the whole system to 64 bit version company wide.

I don't know the exact cause of the problem why it happened? I re-studied the whole database and fixed the problem by just introducing PRIMARY KEYS to all the tables in the database.

I think this is the MAJOR problem when you are make relationship with the others tables without having the PRIMARY key to the MASTER table in ACCESS 2010.

Katie Mitchell:

I have re-seeded the auto-number field with the following code:

Steps:

• 
  Click create >>> Module >>> and open the VBA code editor in MS-Access 2010
  Paste the following code
  save..

Option Compare Database

Function AutoNumFix() As Long
    'Purpose:   Find and optionally fix tables in current project where
    '               Autonumber is negative or below actual values.
    'Return:    Number of tables where seed was reset.
    'Reply to dialog: Yes = change table. No = skip table. Cancel = quit searching.
    'Note:    Requires reference to Microsoft ADO Ext. library.
    Dim cat As New ADOX.Catalog 'Catalog of current project.
    Dim tbl As ADOX.Table       'Each table.
    Dim col As ADOX.Column      'Each field
    Dim varMaxID As Variant     'Highest existing field value.
    Dim lngOldSeed As Long      'Seed found.
    Dim lngNewSeed As Long      'Seed after change.
    Dim strTable As String      'Name of table.
    Dim strMsg As String        'MsgBox message.
    Dim lngAnswer As Long       'Response to MsgBox.
    Dim lngKt As Long           'Count of changes.
    
    Set cat.ActiveConnection = CurrentProject.Connection
    'Loop through all tables.
    For Each tbl In cat.Tables
        lngAnswer = 0&
        If tbl.Type = "TABLE" Then  'Not views.
            strTable = tbl.Name     'Not system/temp tables.
            If Left(strTable, 4) <> "Msys" And Left(strTable, 1) <> "~" Then
                'Find the AutoNumber column.
                For Each col In tbl.Columns
                    If col.Properties("Autoincrement") Then
                        If col.Type = adInteger Then
                            'Is seed negative or below existing values?
                            lngOldSeed = col.Properties("Seed")
                            varMaxID = DMax("[" & col.Name & "]", "[" & strTable & "]")
                            If lngOldSeed < 0& Or lngOldSeed <= varMaxID Then
                                'Offer the next available value above 0.
                                lngNewSeed = Nz(varMaxID, 0) + 1&
                                If lngNewSeed < 1& Then
                                    lngNewSeed = 1&
                                End If
                                'Get confirmation before changing this table.
                                strMsg = "Table:" & vbTab & strTable & vbCrLf & _
                                    "Field:" & vbTab & col.Name & vbCrLf & _
                                    "Max:  " & vbTab & varMaxID & vbCrLf & _
                                    "Seed: " & vbTab & col.Properties("Seed") & _
                                    vbCrLf & vbCrLf & "Reset seed to " & lngNewSeed & "?"
                                lngAnswer = MsgBox(strMsg, vbYesNoCancel + vbQuestion, _
                                    "Alter the AutoNumber for this table?")
                                If lngAnswer = vbYes Then   'Set the value.
                                    col.Properties("Seed") = lngNewSeed
                                    lngKt = lngKt + 1&
                                    'Write a trail in the Immediate Window.
                                    Debug.Print strTable, col.Name, lngOldSeed, " => " & lngNewSeed
                                End If
                            End If
                        End If
                        Exit For 'Table can have only one AutoNumber.
                    End If
                Next    'Next column
            End If
        End If
        'If the user chose Cancel, no more tables.
        If lngAnswer = vbCancel Then
            Exit For
        End If
    Next    'Next table.
    
    'Clean up
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    AutoNumFix = lngKt
End Function

run the code, it will re-arrange the seeding and should fix the problem.

My recommendation is you re-consider all the tables and add PRIMARY KEY to all the tables specially to those which have relationship link with other secondary tables.

HTH.

 

[Khalid_Afridi]

The problem is solved by running the AutoNumFix function already posted in the thread and adding PRIMARY key to all the related tables corrupted for auto number seeding.

 

[Alan White]

Khalid,
I posted a Quick Reply but don't know what happend to it?
To stop Autonumber field corrupting with MsAcces 2010 compact ensure the Autonumber field is the Primary Key Index with Acending order with not other indexes on the field. It will corrupt allowing duplicated key even if the Autonumber has no Indexes.
Microsoft has a problem with the MsAccess 2010 compact - who knows when they will recognise it and fix it. Just be careful and ensure all Autonumber field are the Primary Key in Ascending order only. Test with Allen Browns solution - to find the corrpted tables but you will have to manually fix the Index on the table or it will corrupt the next time you compact it.

Regards,
Alan White

 

[Khalid_Afridi]

Alen,
Thanks for suggestions. As I mentioned earlier, by applying Primary Key to each table (and by rule every table should have one primary key) specially to the table which have relationship one to one or one to many relationship, must have a primary key. In Access 2010, this problem come mostly to those Tables which don't have Primary Key. I use every possible way to get rid the autonumber corruption problem and at last I reached to the conclusion that PRIMARY KEY is the only solution for this MONKEY PLAY
Thanks to Allen Browns solution which fixed the Autonumber key, but this is also not a permanent solution, because when you compact and repair, again it will corrupt the Autonumber Key, so the permanent solution is to introduce the PRIMARY KEY to the tables

 

[techexpressinc]

Thanks for sharing your experiences.

Russ

 

[Khalid_Afridi]

Welcome, Russ