How can I change the KeyID for one of my tables with minimal impact?

I have an Access split database with many queries, forms & reports and need to change the Key Id name for one of my primary tables. How can I change the name from the generic ID to CSSID without going in every form and query I.E. updating manually. Is there an easier way?
DJPr0Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

PatHartmanCommented:
Using a utility to change ID to CSSID globally will lead to lots of incorrect changes since "ID" will be part of may words.  This is a case where I would use the Access "Name AutoCorrect" (some call it AutoCorrupt for good reason) to do the bulk of the change.  This tool will NOT change VBA or expressions but it will propagate column name changes from a table to queries, forms, and reports.  So, it get's you part of the way there.  The rest you will have to do manually.  The key will be understanding how Name Autocorrect works so you can control it.

First, you'll need to import the table you want to change back into the FE since this will not work on a linked table.  The alternative is to make a copy of the schema locally and remove the link to the BE.  But BEFORE you attempt this, you MUST first turn off Name Auto Correct AND make at least two backups of the app - both FE and BE.  I'm going to attach  a MS document that explains how the feature works.  Read it very carefully.   Also attached is a PP that I made to summarize the document for a presentation to my Access User's Group.  The database is a sample to play with to see how Name Auto Correct works.

The most important thing you need to understand is:
AutoCorrect isn’t attempted until the next time an object is opened so if multiple layers of changes are made, results can be incorrect.  This is what gets people in trouble.  Once you get ready to start and change the column name in the table, you have to open every single object that touches the table to ensure that the change is propagated immediately.  Then when you are done, you can turn off NameAutoCorrect untill the next time you have a large change that you want help with.  It is way too dangerous to leave on all the time.  If you are inclined to rename old objects xxxMyObject and create new versions, Name Auto correct will kill you because it will happily change references from MyObject to xxxMyObject, silently and by the time you discover it, you'll have a huge mess to clean up.
NameAutoCorrect.doc
NameAutocorrect.ppt
NameAutoCorrectSampleDB.zip
1

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
DJPr0Author Commented:
Thanks Pat
0
PatHartmanCommented:
You're welcome.  If that helped, please close the question.
0
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
Databases

From novice to tech pro — start learning today.