Corrupt Autonumber in Field

[Guest]

Hi,

Wonder if someone can help me please.

I have designed and created a database to log tests carried out on types of
forms that we design.

The database consists of only two tables, the main one housing all the form
data and the other showing each test plus any additional notes that the user
wishes to add.

Linking these tables on a one to many relationship is a unique test ID which
in the main table is an autonumber key field.

For some reason this has now become corrupt and instead of the next field
showing for example 414, it shows 2796467.

Can anyone help, I haven't seen this before, nor do I know of a way to
repair it.

I've tried taking out the field and adding it again to reset the field which
works, but the problem is that the additional notes then do not link to the
correct test, because this still shows the original unique test ID.

Many thanks

Chris

 

[kingston via AccessMonster.com]

Delete the corrupt record, compact and repair the database, and create an
append query to force the correct number into the table. HTH

 

[Guest]

Thanks for this.

I've tried it but unfortunately this doesn't seem to work.

Any ideas please?

Regards

Chris

 

[kingston via AccessMonster.com]

Try importing the data into a different database (minus the corrupt record).
Are you getting the same bad record number every time you try to fix the
table? Is it the first thing that happens or is it happening when you're
doing something in a form or query? IOW, try to fix it and immediately add a
record to the corrupted table.

Thanks for this.

I've tried it but unfortunately this doesn't seem to work.

Any ideas please?

Regards

Chris

Delete the corrupt record, compact and repair the database, and create an
append query to force the correct number into the table. HTH

[quoted text clipped - 26 lines]

 

[Tony Toews]

For some reason this has now become corrupt and instead of the next field
showing for example 414, it shows 2796467.

There are two possible problems. Now are the users actually seeing
this number? If so autonumbers aren't meant for the users to see
them..

One problem may be that the autonumber New Values field is set to
Random rather than Increment. If this is the case then you will have
to delete the field and recreate it. If this field is participating
in a relationship and is the parent table then things become much more
challenging.

However in some situations the seed value for the autonumber field can
get corrupted.

Resetting AutoNumbers
http://www.allenbrowne.com/ser-40.html

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database
http://support.microsoft.com/?kbid=257408

AutoNumber Field Duplicates Previous Values After You Compact and
Repair a Database
http://support.microsoft.com?kbid=291162

How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
Database Engine
http://support.microsoft.com/?id=239114

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Joseph Meehan]

Hi,

Wonder if someone can help me please.

I have designed and created a database to log tests carried out on
types of forms that we design.

The database consists of only two tables, the main one housing all
the form data and the other showing each test plus any additional
notes that the user wishes to add.

Linking these tables on a one to many relationship is a unique test
ID which in the main table is an autonumber key field.

For some reason this has now become corrupt and instead of the next
field showing for example 414, it shows 2796467.

Can anyone help, I haven't seen this before, nor do I know of a way to
repair it.

I've tried taking out the field and adding it again to reset the
field which works, but the problem is that the additional notes then
do not link to the correct test, because this still shows the
original unique test ID.

Many thanks

Chris

I don't have any suggestion other than those already offered for your
current problem, but I will suggest that autonumbers are not intended for
human consumption. They are only intended to provide unique numbers and
while they usually are in order, there is not guarantee that they will be
issued in any specific order. When they are not, and people see them, they
tend to get confused and concerned even if it does not really make a
difference for their use.

 

[aaron.kempf]

****ing retards around here

if autonumber isn't reliable-- then change backends.

MDB is for retards and newbies that don't have the mental capacity to
learn SQL Server

-Aaron

 

[aaron.kempf]

AutoNumbers ARE designed for human consumption.

your bullshit database format is the root of the problem.

you just need to use Access Data Projects instead of your obsolete MDB
_CRAP_

-Aaron

 

[Joseph Meehan]

AutoNumbers ARE designed for human consumption.

your bullshit database format is the root of the problem.

you just need to use Access Data Projects instead of your obsolete MDB
_CRAP_

-Aaron

Again, thank you for your highly intelligent thoughtful advice.

 

[aaron.kempf]

eat shit mother ****er

if youre database can't reliably MAKE AUTONUMBERS.. then do this:

a) GET A NEW DATABASE
b) GET A NEW DATABASE
c) GET A NEW DATABASE

 

[Alan]

Hi Chris,

You can try our product Advanced Access Repair to repair your Microsoft
Access databases. It is a powerful Access recovery tool that can
recover the structure and records of the tables in your Access
databases.

Please visit http://www.datanumen.com/aar/index.htm for detailed
information about Advanced Access Repair.

And you can also download a free demo version at
http://www.datanumen.com/aar/aar.exe to see if our product can help
you.

Alan Chen
DataNumen, Inc. - World leader in data recovery technologies
Website: http://www.datanumen.com
Fax: +1-800-9917-FAX (US Toll-Free), +852-31829286 (HONG KONG)

 

[aaron.kempf]

I just reccomend using a REAL database


MDB is for wimps; you always got people trying to sell you on
workarounds and fixes.

but the root of the matter is that MDB is a piece of shit ass database
and anyone that uses it for anything should be FIRED and SPIT UPON.

-Aaron
ADP Nationalist