MS Access PK Text Value

Is there any disadvantage in using a text value as the PK in a  table?  I have always used AutoNumber values as the PK however I am converting data where the PK in the tables is alpha/numeric like AAA1234567.  I thought about using an AutoNumber asnd just looking up the alpha value as needed but there are a number of related tables and I would need to modify them all.

I can create the next value by getting the "AAA" alpha part and just incrementing the 1234567 part so creating the key is not an issue.

Thoughts or suggestions?
LVL 14
Bill RossProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No real problems,   Just the length of the key. With what you have, you'll be fine.

Jim

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
Other than performance, no.
PatHartmanCommented:
Performance doesn't become an issue until you get to huge row counts unless your text PK is longer than about 10 characters.

The problem in this case is that the PK contains multiple attributes and you will have to constantly split and concatenate the field to work with it.  Separating the constituent parts is better but that gives you a multi-field PK which is a pain to work with and will still force you to update all the sub tables.

Personally, I would go with an autonumber PK.  To do the conversion, you would convert the parent table first to create the PK.  Then for the child tables, you would need to join to the new parent table on the old PK field but append the autonumber value as the FK for the child table.  So once you understand how to do it, the conversion is actually painless.

If the numeric part of the current PK is unique regardless of the alpha part, you can simplify your process and insert the old number part as the PK to the new table.  An append query is the ONLY place where you can actually specify a value for an autonumber.  Without being able to do that, conversions would be a nightmare.  Then, instead of keeping the whole concatenated key in the record, the number would be the PK and the alpha would simply be a data field.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.