Corrupted Autnumber property

P

Pietro

Hi all,

I have a table that contains an autonumber field,it was working well,bu
suddenly when i start to add a new record in a form that depends on this
table,the autonumber gives the record number 0,if i add one more takes number
1,but if this number already exists,i get an error message and can no longer
add records.
What can i do to solve this problem
 
A

Arvin Meyer [MVP]

Pietro said:
I have a table that contains an autonumber field,it was working
well,bu suddenly when i start to add a new record in a form that
depends on this table,the autonumber gives the record number 0,if i
add one more takes number 1,but if this number already exists,i get
an error message and can no longer add records.
What can i do to solve this problem
Click to expand...

First make sure you have all your service packs installed. This is a bug
which first surfaced in Access 2000, and has been fixed with a Service pack
release. It is not hard to fix, but is time consuming and requires a
thorough knowledge of your database. ALWAYS WORK ON A COPY:

1. Compact your database

2. Located all the tables which have a Foreign Key based upon your problem
table's autonumber Primary Key

3. Add a new column, type Long Integer, to each of those tables.

4. Change your corrupted autonumber Primary Key to a Long Integer.

5. Add a new column, type Autonumber, to the problem table.

6. Run an Update query to each of the subsidiary tables to update the empty
column in each of those tables.

7. Rename all the old columns in all the tables.

8. Name all the new columns to the original names, and change the Primary
Key from the old key to the new autonumber key.

9. Once you are satisfied that the queries will work, you can delete the old
columns, and make sure all the indexes and relationships are recreated.

The method above works perfectly if done in the specific order shown. I've
had to do this fix 4 times, but there has not been a problem once the
service packs have been deployed.
 
T

Tony Toews [MVP]

Pietro said:
I have a table that contains an autonumber field,it was working well,bu
suddenly when i start to add a new record in a form that depends on this
table,the autonumber gives the record number 0,if i add one more takes number
1,but if this number already exists,i get an error message and can no longer
add records.
Click to expand...

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

You may receive an error message when you try to insert a new record
in a table that contains an Autonumber field in Access 2007 or in
Access 2003
http://support.microsoft.com/?id=884185

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
D

dbahooker

good stuff

to me?

I jsut think that the whole idea of compact and repair is goddamn
ridiculous
have you guys never had to wait an hour while it compacts a 1.5gb
database?

you just need to move to Acceess Data Proejcts
ADP allows you to reset your autonumbers using the IDENTITY and SEED
properties.. additionally you can reset a particular table by using
the TRUNCATE statement

And if worst comes to worst; and you need to insert an explicit value
into this autonumber column; you're allowed to run

SET IDENTITY_INSERT tableName ON


this isn't possilbe in Access MDB
MDB shouldn't be used in the real world

NEVER not for a single user and a single record
 
R

RoyVidar

<[email protected]>:

Just in case someone actually reads their rants...

[snipped junk]
ADP allows you to reset your autonumbers using the IDENTITY and SEED
properties.. additionally you can reset a particular table by using
the TRUNCATE statement
Click to expand...

Has been available for Jet since 4.0 (which is the same time as ADPs
have been around). Check out Allen Brownes stuff in the link given
by Tony Toews. This can also be done through DDL (which is demonstrated
in at least one of the MS KBs Tony Toews linked to).
And if worst comes to worst; and you need to insert an explicit value
into this autonumber column; you're allowed to run

SET IDENTITY_INSERT tableName ON


this isn't possilbe in Access MDB
Click to expand...

Not only is it possible, it has been possible as long as I can
remember,
(I can't remember further than Access 97, though). Anyway, with
Access/Jet, you don't even need to fire off some settings stuff, you
just do a simple

INSERT INTO myTable (myAuto, myText) Values (5, 'test')
 
D

dbahooker

What.. so you kids are going to use TWO data access libaries instead
of ONE?

ROFL

good stuff kid; you MDB kids don't get to claim both libraries

ADO + ADP = LONG LIVE MS
DAO + MDB = INFATADA





<[email protected]>:

Just in case someone actually reads their rants...

[snipped junk]
ADP allows you to reset your autonumbers using the IDENTITY and SEED
properties.. additionally you can reset a particular table by using
the TRUNCATE statement
Click to expand...

Has been available for Jet since 4.0 (which is the same time as ADPs
have been around). Check out Allen Brownes stuff in the link given
by Tony Toews. This can also be done through DDL (which is demonstrated
in at least one of the MS KBs Tony Toews linked to).
And if worst comes to worst; and you need to insert an explicit value
into this autonumber column; you're allowed to run
Click to expand...
SET IDENTITY_INSERT tableName ON
Click to expand...
this isn't possilbe in Access MDB
Click to expand...

Not only is it possible, it has been possible as long as I can
remember,
(I can't remember further than Access 97, though). Anyway, with
Access/Jet, you don't even need to fire off some settings stuff, you
just do a simple

INSERT INTO myTable (myAuto, myText) Values (5, 'test')
Click to expand...
 
Top