Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004

    Unanswered: Change field name in the table

    I want to change two fields name in the table. Is that way i can change all the field name in the query, form and report. Do i have to change manually. That would be take long time. Thanks.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    As far as Im aware there is no easy way to change a column name in a table and automatically ripple those changes though existing queries, forms and reports. You may be able to cook up some vba to go through the query defs collection and make changes there, but I doubt you will be able to make those same changes to reports and forms.

    It just goes to underpin that you need to think very carefully about the data design before succumbing to the temptation to start designing forms, queries & reports.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    Access 2003 has something called Object Dependencies which may help you identify which forms/reports need modifying if you have Access 2003:

    There is also another product called: FMS Analyzer which you may want to research but you'll need to pay for this product. I believe it helps with dependency objects.
    Last edited by pkstormy; 08-07-07 at 20:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jul 2003
    Sadly, I've done this several times.

    If you're pretty good with vba, and know your collections, it wouldn't take too long to build something to open each report and form and find the object that use that field as a source (don't forget to test rowsources for combo boxes and such). Queries wouldn't be bad either.
    I've never tried using code to look through modules, though. Not saying it can't be done, I've just never tried it. In that case, just use Ctl+H on the whole project.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Maybe I don't understand the OP's question, but with Perform Name AutoCorrect checked in Options, you can create a table with, say, a field named OldField, then create a form, report and query all using this field, go back into Design View for the table and change the name of OldField to NewField, and the name will be changed in all of these objects and they will continue working just like before. On the form, the Control Source for the textbox holding the field will be changed, and so any calculated controls involving the field will also still function.

    Because of the performance hit caused by AutoCorrect, I turn it off before deploying an app, but it can come in handy during development.

    Last edited by Missinglinq; 08-07-07 at 23:16.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    The "Name Autocorrupt" feature will sometimes be able to propagate altered names through standard queries, forms and reports, but it

    * sometimes corrupts parts of, or the whole db (here's some info
    * will not work with SQL, recordsources, controlsources, rowsources or any usage those fields through VBA
    * might have problems with SQL strings assigned to forms, reports or controls
    * have problems with subqueries
    * is generally be to flaky to be relied upon (my opinion)

    There are commercial stuff available. pkstormy mentions one (, then there's also, which still doesn't work with 2003 unless you have a previous version installed on the computer, and, which I think is either shareware or freeware.

    Not that I've used any of them, but from what I read, they will all be more reliable than using the "Auto Corrupt" feature (they will also find matches in subqueries, dynamic SQL and VBA...), but changing name in the middle of the project, will probably never be without risk.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts