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

    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
    Location
    out on a limb
    Posts
    13,692
    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
    Location
    Madison, WI
    Posts
    3,926
    Access 2003 has something called Object Dependencies which may help you identify which forms/reports need modifying if you have Access 2003:

    http://office.microsoft.com/en-us/ac...714971033.aspx

    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
    Location
    Michigan
    Posts
    1,941
    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
    Location
    Richmond, Virginia USA
    Posts
    2,763
    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.

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

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

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    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 http://allenbrowne.com/bug-03.html)
    * 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 (http://www.fmsinc.com/), then there's also http://www.moshannon.com/, which still doesn't work with 2003 unless you have a previous version installed on the computer, and http://www.rickworld.com/download.html, 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.
    Roy-Vidar

Posting Permissions

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