Autonumbers added to existing table?

[Patti]

Is there a way to add an autonumber field as the primary
key to an already existing table. I created a database
last year and, after becoming a bit more educated on
normalization, etc. realize that it would have been better
to use autonumber fields as the primary keys. On some
tables I have used 2 or more fields as the primary key
which is not always effiecient. I'd hate to start
completely over since I have historical data dating back
to 1995. The database actually works OK the way it is, but
I can see several ways it could be "cleaned up", but then
it would probably affect all my forms and reports????? Any
help would be appreciated!

 

[Tim Ferguson]

The database actually works OK the way it is, but
I can see several ways it could be "cleaned up", but then
it would probably affect all my forms and reports?????

"... if it ain't broke, don't fix it."

You can of course just use the table design window to add an Autonumber
column, but then you are right that you'll have a great deal of work
redoing the relationships, queries, forms and reports. Don't forget any
buried queries in RowSource properties in list box controls, and bits of
forgotten query in vba modules. Do remember to do it on a copy of the
database and backup regularly as you are going along.

As long as you are clear how much this will improve things for the users,
then it may be worth the work.

Best of luck

Tim F

 

[Guest]

How does using an autonumber relate to normalization?

Using an autonumber is a terrible idea. Autonumber is
proprietary and does not have any meaningful relation to
the data. Read anything by Joe Celko. It also exposes
the physical implementation to the user, which is never a
good idea. I would leave things alone since you already
have defined keys that are not autonumber. If your keys
span a number of columns then you have not modeled the
data correctly.

 

[TC]

Excellent troll!

TC

How does using an autonumber relate to normalization?
Using an autonumber is a terrible idea. Autonumber is
proprietary and does not have any meaningful relation to
the data. Read anything by Joe Celko. It also exposes
the physical implementation to the user, which is never a
good idea. I would leave things alone since you already
have defined keys that are not autonumber. If your keys
span a number of columns then you have not modeled the
data correctly.

(snip)