Change the name of Primary Key

[JS]

I have a database that has thousands of records. In my main table I have a
primary key named "Social Security." I was asked if I could change that name
to "Employee ID" since my company no longer uses the social security number
as an employee identifier.

Can this be done? If so, how hard will it be?

Thanks for any suggestions you may have.
JS

 

[Ken Snell \(MVP\)]

Open the table in design view. Change the name of that field.

 

[Craig Hornish]

Wow - the easy answer from a MVP - now for a better answer.

Open the table in design view. Change the name of that field.

Now for the extra stuff:
Open every query that uses that table (also the ones in the forms and
reports) and change the name of the fields that had the Social Security and
change them.
In the forms and reports - change all Control Sources in controls that use
this.
In any VBA code that uses SQL statements you will need to change that.

And now for what should also be done:
Unfortunatly if the Social security number was really used as the
linking field between this table and other tables you have a slight security
risk and probably other issues that should be resolved.
One question you should ask is -- Do you want to keep the Social
Security number anyways as a unique identifiyer and just ad another field
EmployeeID? It will depend on how you lookup people and make sure you have
the correct person.
You probably should have an idenitfier (primaryID) that is not seen that
will link the (what I assume) Employee table with any other table. An
either have a EmpoyeeID that is visible to anyone -if that is how you look
people up or just by the persons name and other information to confirm the
person.
Anyway you go with this will be not be just as simple a changing the
name and having everything work the way it has alwasy worked.

Craig Hornish
chornish at cap-associates.com

 

[Duane Hookom]

If there are lots of forms, related tables, reports, code, queries,...
consider a third party utility such as those listed in the "Scan and Replace
Utilities" on Tony Toews page
http://www.granite.ab.ca/access/thirdparty.htm.

 

[Ken Snell \(MVP\)]

< chuckle >

Figured we'd start with the easy answer, and then get more complex....

 

[JS]

Thank you for explaining in great detail! I appreciate your response! I will
analyze your suggestions before I begin.

 

[Craig Hornish]

Hi Ken,
I don't want to make a big deal out of this but I do hope to get a
point across.

The questioner is almost obvious a novice - and would have probably
done what you said
without anything further done to the application. But, then the application
is "broke", and has to come back and ask why? And may ask why didn't you
tell me this first? And we use this program throughout the day and it can't
be down for that long. etc...

It might not be as serious as say having a corrupt database and
suggesting repairing it, or
some other action and then later saying by the way you should make sure you
backup up the
database first.

Sometimes you need to qualify simple answers because they can cause
more problems if you don't know all of the ramifications.

Craig Hornish

 

[Ken Snell \(MVP\)]

Your point is well-spoken... I appreciate your input.
Thanks.

 

[Tim Ferguson]

I have a database that has thousands of records. In my main table I
have a primary key named "Social Security." I was asked if I could
change that name to "Employee ID" since my company no longer uses the
social security number as an employee identifier.

Answer 2: Just don't. "The company" has no need to go sniffing round field
names in tables; they should be looking at labels on forms and reports, and
at column labels in queries so that is where you should be where you should
be putting your efforts.

Would you go back to Ford and ask them to change the colour of the wiring
going to the petrol pump because you don't think purple and green go
together well?

All the best


Tim F