|
 |
access thread: Primary Keys
Message #1 by Paul Bucknell <paul@d...> on Sun, 27 Jan 2002 20:16:20 +0000
|
|
I have a database that has had data added to and deleted for a long time.
Subsequently over this time, the Primary Key (AutoNumber) has jumped
from "in the thousands" to "in the billions" and although it doesn't really
matter
I was wondering if there was a way of re-indexing the AutoNumber to something
more fluid and sequential.
Regards
Paul Bucknell
Message #2 by Paul Streeter <PStreeter@C...> on Sun, 27 Jan 2002 14:46:28 -0600
|
|
Paul Bucknell wrote:
> I have a database that has had data added to and deleted for a long time.
> Subsequently over this time, the Primary Key (AutoNumber) has jumped
> from "in the thousands" to "in the billions" and although it doesn't
> really matter
> I was wondering if there was a way of re-indexing the AutoNumber to
> something
> more fluid and sequential.
Yes, but I'm not quite sure how I did it. Maybe compacting works, If
not, deleting the field, then re-creating it does.
Message #3 by "dave sharpe" <pmmgpgp@y...> on Sun, 27 Jan 2002 16:17:59 -0500
|
|
Paul - Its something that access doesn't want you to do.
I think that this will do it for you:
1-Make a temp table using your "real" table as the source.
2-Delete the records from your "real' table.
3-Append your records back into your "real" table.
( you can't append the autonumbered field, it will be
rebuilt from zero ).
4- delete the temp table.
Dave
----- Original Message -----
From: "Paul Bucknell" <paul@d...>
To: "Access" <access@p...>
Sent: Sunday, January 27, 2002 3:16 PM
Subject: [access] Primary Keys
I have a database that has had data added to and deleted for a long time.
Subsequently over this time, the Primary Key (AutoNumber) has jumped
from "in the thousands" to "in the billions" and although it doesn't really
matter
I was wondering if there was a way of re-indexing the AutoNumber to
something
more fluid and sequential.
Regards
Paul Bucknell
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Message #4 by "Richard Lobel" <richard@a...> on Mon, 28 Jan 2002 00:10:24 -0800
|
|
Paul,
Dave's reply to you will work. Just want to give you something to think
about before implementing it. Have you had the primary key written to
other tables with relationships? You could end up with junk data and
lots of orphans if you renumber the one table and not the many table(s).
Just something to think about from one who has learned the hard way.
Richard Lobel
Accessible Data
richard@a... <mailto:richard@a...>
Cell: (xxx) xxx-xxxx
Fax: (xxx) xxx-xxxx
|
|
|
View Cart
