Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have a Master Record with autonumber (New Values = Increment)

    The past 5 records entered have the following values in automenber lngApplicantID:
    5158
    5159
    5160
    338231297
    338231298

    I did a Compact and Repair and the next automumber on a new record is 338231299

    1. Why did autonumber jump from 5160 to 338231297?

    2. How can I adjust 338231297 & 8 back to a 4 digit number?

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    AutoNumbers *are* wacky in Access 2000. You'll probably never find out what caused this particular glitch.
    An autonumber is a meaningless unique identifier, so these values shouldn't be a problem. If you still want to 'repair' them, you'll have to delete the records and
    You'll probab;y have to delete the two records with high autonumber values, compact the database, then add them again.
    Moderator Mark Liquorman has a utility to reset autonumber values (shareware) - see Liquorman, Inc.: Access Tips, Links, & Downloads.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I did a Make Table with the 2 bad records without the autonumber

    I deleted the 2 records from the main table in question

    I did a Detect and Repair on the DB with table in question

    I Appended the 2 records less the autonumber back to the original table and it reassigned the following autonumbers:

    338231299
    338231300

    Still same problem, any other thoughts?

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Just removing the records, compacting the database (I assume you meant that) and adding the records (sans autonumber field) again won't help. This is a 'bug' or 'feature' in recent versions of the Jet engine. Have you tried Mark Liquorman's utility? Alternatively, use one of the methods from MSKB article How to reset an AutoNumber field value in Access.

  5. #5
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have a database that does the same thing periodically about once per month. Users don't want a higher number even though it is just an identifier even if it really does not matter. I have a form I use to reset the seed of the autonumber field to the next highest number in the series. I am not sure where the function came from I found it in an existing database somewhere and it was not credited to anyone.
    Here is the function:
    Public Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
    'You must pass the following variables to this function.
    'strTbl = Table containing autonumber field
    'strCol = Name of the autonumber field
    'lngSeed = Long integer value you want to use for next AutoNumber.

    Dim cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim col As ADOX.Column

    'Set connection and catalog to current database.
    Set cnn = CurrentProject.Connection
    cat.ActiveConnection = cnn

    Set col = cat.Tables(strTbl).Columns(strCol)

    col.Properties("Seed") = lngSeed
    cat.Tables(strTbl).Columns.Refresh
    If col.Properties("Seed") = lngSeed Then
    ChangeSeed = True
    Else
    ChangeSeed = False
    End If
    Set col = Nothing
    Set cat = Nothing
    Set cnn = Nothing

    End Function

    Create a form with a text box called NewHighestNumber and add a command button with the following code on the click event:

    If ChangeSeed("Vessels", "ID", NewHighestNumber) Then
    MsgBox "Done"
    End If

    Carla

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Carla

    Try to implement above code, have a reference to Microsoft ActiveX Data Objects 2.1 Library

    Getting Compile Error

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    To the ADOX library: Microsoft ADO Ext. 2.7 for DDL and Security. The version number might be slightly different.

  8. #8
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The library Hans posted is the same one I am using.

    Carla

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks Hans

    The form and code worked OK from the BE, what would I have to change to run the same form and code from the FE?

    When I try it from the FE I get:

    Run-time error

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Instead of opening a connection to the current database

    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection

    open a connection to the backend database

    Dim cnn As New ADODB.Connection
    cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4. 0;Data Source=CatabasesBE.mdb"

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Carla

    Your code is exactly what I needed

    1. I did a Make Table Query with the 2 bad autonumber records without the autonumber

    2. I deleted the 2 records with the bad autonumbers from the main table in question

    3. I did a Detect and Repair on the DB with table in question

    4. I ran your form to reset the autonumber

    5. I Appended the 2 records less the autonumber back to the original table and it reassigned the correct autonumbers:

    Must be a nightmare if this happens on a monthly basis for you

    What kind of strategy do you use to reconnect child records back to the correct parent records with the new autonumbers?

    Thanks, John

  12. #12
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    John,

    There are a very limited group of users in this database, only 7 and they notice if the number is not sequential as soon as they enter the first field of information. Two of the users know how to open the reset seed form and enter the next sequential number. This has worked well for about 4 years. This problem appeared when the database was migrated from Access 97 to Access 2000. The future plan is to migrate this backend to SQL Server before the end of the year so I think this should solve future problems.

    Carla

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    This worked OK

    If I add the reference:

    ADOX library: Microsoft ADO Ext. 2.8 for DDL and Security

    To my production Access 2000 FE, what are the chances that it will correctly automatically register when I distribute next updates to my existing clients with this FE who are using Access2000, 2002 and 2003?

    Thanks, John

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    As far as can tell, the different versions all resolve to msadox.dll (without a version number in the file name), so I think it'll be OK whichever Access version the user has.

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Wacky Autonumber? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks

Page 1 of 2 12 LastLast

Posting Permissions

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