Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008

    Unanswered: Complications from Renaming a table in Access

    Hi All
    I recently learned (the hard way) that renaming a Table in access is not such a good idea, since it seems to screw up all resulting queries, reports, etc. So my issue is:

    I built this huge, complicated database last year that I want to use again this year. But I named a bunch of my tables things like "2008 Monitoring Data" and "2008 Vegetation Data" etc, which, since we are in 2009, now doesn't make sense. If I had known then what I know know, I could have just left the year off, and the database would make a great template for years to come, but alas, I didn't. (My approach to using access is to just assume it can do everything I think it should be able to do, like when re-naming a table it would just update the rest of the universe accordingly. Probably not such a good approach but there you go.)

    So the thing is, when I recently renamed a table in a much smaller project, it caused everything to blow up, and no queries would run, etc. So I was expecting a similar issue with my "template" database. Just for fun, I went ahead and renamed everything in (a copy of) of my template database, just to see what kind of carnage would result from such a foolish action.

    The WEIRD thing seems to be working fine!!!! However, I am afraid to take this as my template and move on down the road because I'm guessing this will come back to bite me in the.. well it will crash later on.

    So my question to all you lovely database people out there is:
    What do you think? Can I trust my DB with the renamed tables to work from here on out if it didn't already explode on me?

    Thanks for your input! Sorry this is so long winded!

  2. #2
    Join Date
    Mar 2007
    Name AutoCorrect was built to handle just what you describe but it has drawbacks that cause most developers to turn it off. Failures caused by Name AutoCorrect
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    the smart alec reply is that your initial design was flawed. you probably should have moved the year into the relevant tables as a column, that way round you will not need to rename any tables in future.

    however when ever you make changes to underlying tables then you need to make certain that you retest the application just in case.

    that means running every query tht accessed the table or column, every report and every form
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2008
    Thanks guys! Based on the link RG provided it looks like Name AutoCorrect is not the holy grail I am looking for. (But it would be if it worked?)

    I did do what healdem suggested and ran all my queries and reports, waited for the error messages, and found all the places (I hope!) where it was referenced and updated it. We'll see what happens from here!

  5. #5
    Join Date
    Nov 2008
    Turns out that Name Auto Correct was actually ON all along, so I guess that demonstrates how well it [doesn't] work! :0
    Thanks again!

  6. #6
    Join Date
    Nov 2007
    Adelaide, South Australia
    I'd be turning that OFF.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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