access - primary key table problem

G

Guest

On a table with 1004 records - when I open the record, it knows there are
1004 but when trying to add a new one, the primary key index does not assign
1005 but rather goes to 1 (and of course it won't store because of a
duplicate key - thank goodness). Exit the table, repeat above, and this time
it points to 2 ...and so on...
Does anyone know how to reset the index to the bottom of the table or
replace the table with a corrected one without having to go thru the whole
database and change all the record connections and field names? Thanks.
 
R

RoyVidar

journigirl wrote in message
On a table with 1004 records - when I open the record, it knows there
are 1004 but when trying to add a new one, the primary key index
does not assign 1005 but rather goes to 1 (and of course it won't
store because of a duplicate key - thank goodness). Exit the table,
repeat above, and this time it points to 2 ...and so on...
Does anyone know how to reset the index to the bottom of the table or
replace the table with a corrected one without having to go thru the
whole database and change all the record connections and field
names? Thanks.
Click to expand...

Autonumbers aren't meant to be sequental numbers. You are expected to
see intervalls due to cancelled records etc, but having it restarting
from one isn't that common. Check out this link
http://support.microsoft.com/default.aspx?scid=kb;en-us;884185
Perhaps try it out on a copy? I think, if this table is a parent table
in a parent-child relationship, you will need to remove the
relationships while doing this.
 
D

Douglas J Steele

RoyVidar said:
journigirl wrote in message


Autonumbers aren't meant to be sequental numbers. You are expected to
see intervalls due to cancelled records etc, but having it restarting
from one isn't that common. Check out this link
http://support.microsoft.com/default.aspx?scid=kb;en-us;884185
Perhaps try it out on a copy? I think, if this table is a parent table
in a parent-child relationship, you will need to remove the
relationships while doing this.
Click to expand...

And if it's not an Access 2003 database, have all the service packs been
installed? This was definitely a problem when Access 2000 was first
released, but it was supposedly corrected in one of the SPs.
 
G

Guest

Hi Doug,

Jeff C. (Access Junkie) and I both experienced this issue in an application
that we are beta testing for another individual. Both of our systems are
fully updated, however, it turns out that the autonumber field in question
was created by the person who made this application using an older version of
JET. So, even though our systems were up to date, the service pack fix does
not appear to correct an existing problem.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Duplicate values in the index, primary key, or relationship messag 2
Same record/Primary key in multiple tables 0
MS Access Avoiding redundancy 1
Index/Primary Key Error Handler 1
Adding a primary key 3
Primary key help 2
primary key - concatenated or autonumber? 6
Primary key redifinition? 1

Top