autonumber is generating new numbers that already exist 1

[lamarw]

Hello everyone,
I have reviewed a number of threads on the subject of autonumber. It's all been VERY interesting (i.e. thread181-394005) However, after reviewing these I didn't find anything regarding my problem: autonumber is generating new numbers that already exist in the middle of the database. I have tried doing a compact and repair to no avail. As I am unsure how the system keeps track of the last autonumber used I can't explain why this is happening.
Does the system search the existing records looking for used numbers then generates numbers based on gap's or does it go to the last generated number and go from there?
As a test I imported a copy of the table to a blank database and tried adding a new record there. The autonumber picks up by adding 1 to the LAST generated autonumber, that seems to work fine. Then I went back to the original mdb, renamed the original table, then imported the previously exported table back to the original mdb, now it generates numbers correctly. Obviously, something happened during import but I don't know what.
Additionally, the tables have been split away from the program AND the program and initialized tables are being distributed via MODS package wizard therefore I am linking to the tables EVERY time the program starts. This seems to be working fine but I'm not sure that this isn't contributing to the problem.
While exporting and importing may seem to solve MY problem there are other users with there own set of tables that must be experiencing this problem although they may not know it yet. I need to solve this problem and then provide the solution to the user base.

Any ideas would be appreciated.

I read in a post that changing the method of autonumber to 'Random' ensures that the 'system' generates the number. Is that the same as saying that 'Increment'(default) is not system generated?

Additionally, I read the suggestion of MS on how to set the autonumber to a value that it then uses going forward. If I do this it will have to be in code and run every time the program starts. Not terrible but I'm getting concerned about the startup time.

 

[Golom]

An autonumber field has a "SEED" property that contains the next autonumber that will be generated. You can do things like

ALTER TABLE myTable ALTER COLUMN myANum Count(1,1)

That resets the seed. If you do that when a table has data in it then the next autonumber will be 1 (with the above example) and it may then duplicate a number already in the autonumber field.

Here's some rather convoluted code to find and fix Autonumbers that have a seed problem.

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.
Dim cn                          As New ADODB.Connection
Dim rs                          As ADODB.Recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ".\ChapsMast.mdb;"

Set cat.ActiveConnection = cn
'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")

                        ' Look up the current maximum value in the Auto-number column
                        Set rs = New ADODB.Recordset
                        rs.CursorLocation = adUseClient
                        rs.Open "Select MAX([" & Col.Name & "]) As X From [" & strTable & "]", cn
                        varMaxID = IIf(IsNull(rs![x]), 0, rs![x])
                        rs.Close
                        Set rs = Nothing

                        If lngOldSeed < 0& Or lngOldSeed <= varMaxID Then
                            'Offer the next available value above 0.
                            lngNewSeed = IIf(IsNull(varMaxID), 0, varMaxID) + 1&
                            If lngNewSeed < 1& Then lngNewSeed = 1&
                            '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

 

[lamarw]

Golom,
Many thanks. I used you code with only minor changes and it did indeed find several tables with autonum's in need of fixing. I will continue to test this but I think that this should take care of my problem, again thank you.

Lamar

 

[Golom]

You're welcome ... but there's still the issue of how those autonumbers are getting their seed values screwed up.

I would scan the code for things like ALTER TABLE statements or the SEED property to see if somewhere the system is incorrectly redefining the autonumber.

 

[lamarw]

I have looked through and there are no Alter Table statements nor reference's to 'Seed'.
The data was imported from a foxpro (not VFoxPro) database along wth it's keys. when I first started doing this I noticed that autonumber was actually correctly filling gap's. I assumed that it would continue to do so. Now, after some months of adding more to the tables, I'm suspecting that autonumber does not actually 'manage' the number at all. It finds a starting point and then just mushes on through any existing numbers from then on. Does that sound correct/reasonable to you?

 

[KornGeek]

I have also seen Access do this with autonumbers, and I have been unable to explain why.

faq700-184 gives the argument that autonumbers should not be used and provides an alternative. I believe this is a better way to go.

 

[lamarw]

Hi KornGeek!
I probably didn't make it clear enough that this is a single user environ. A perusal at the FAQ above indicates a multiuser environ. Although the information within is valuable.

Thanks for the reference,

Lamar

 

[JoeAtWork]

Autonumber never tries to recycle or "fill-in" a missing number, it just continues from where it left off.

When you imported, did you fill in the Autonumber field with a value from the source file? If so, that is probably the source of your problem - you should always let Access assign the number.

 

[lamarw]

Hi JoeAtWork!
When I originally set this up I just added the autonumber field. I didn't preset a starting number.

Lamar