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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hans

After further review the client reported that a previously entered record when queried was retrieved with garbage characters in most of the fields.

They ran a BE Compact and Repair and then ran a FE maintenance query that removes incomplete records which got rid of the garbage record.

When they rentered the record this is where they noticed the Autonumber sequence jumpimg from 5160 to 338231297

It looks like what ever happen to corrupt the original garbage record reset the Autonumber seed to 338231297

It also broke the relationship between the Parent and Child table.

I guess this where Mark L

Reply | Quote

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

Reply | Quote

Hi Carla

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

Getting Compile Error

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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=C:DatabasesBE.mdb”

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks

Reply | Quote

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

Carla

Reply | Quote

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

Reply | Quote

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

Reply | Quote