• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 42
  • Last Modified:

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?
0
DJPr0
Asked:
DJPr0
  • 2
1 Solution
 
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
 
DJPr0Author Commented:
Thanks Pat
0
 
PatHartmanCommented:
You're welcome.  If that helped, please close the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now