Question Best way to change the name of an object used in many queries?

[mgibson]

Hi,

I have a query that I would like to change the name of. The query is the backbone of my database and is used by many other queries, forms, and reports. If I change the name using "Rename" then the name changes in the navigation pane but not in all the query/form/reports designs, thus rendering them unusable.

What is the best way to change the name of a query that's used in many other queries, forms, reports without having to redesign everything with the new name?

Thank you for your help, it is very much appreicated.

 

[Mile-O]

Question is, why would you want to change it? Surely the query name isn't visible in any way to the user.

 

[jdraw]

I agree with Mile-0, why?

However, you can copy a query and save it with a new name.

The query is the backbone of my database and is used by many other queries, forms, and reports.

I think this is very rare, and would suggest you may want to do some reading/research on database design.

 

[sxschech]

Here are two programs that can do a find and replace. I use the first one. The second one is free and haven't tried it yet, but has been recommended by others on this forum. As for changing a query name, I don't see why that should be a problem and often find there are cases where a name that made sense at one point doesn't now or needing to point to a different object and renaming is the easiest way to solve it.

Find And Replace, it is around $40, but it does a lot of things, can find and replace information and names in queries, tables, reports, macros, modules, forms. They offer a free trial. http://www.rickworld.com/

--------------------------------------------------------

V-Tools is freeware and does the renaming and many other functions too.

http://www.skrol29.com/us/vtools.php

 

[Pat Hartman]

Name AutoCorrect will do this for you if you understand a little about how it works. If you elect to turn it on, also turn on the log feature so you can see what was changed. It is also a good idea to search the MSDN library for a white paper on Name AutoCorrect. I don't have a link but the article is quite informative and explains how the feature works. This is really important since it doesn't seem to work the way most people think it should work and reading this ahead of time will save you lots of aggrivation and work.

The thing that causes the most problem is that the change is not immediate. That means that if you change the name of an object and save it, no objects that reference it are changed at that time. The objects are not changed until they are opened which might be days or weeks later. To get around this issue, you need to force all objects to open so you can ensure that the change is propagated immediately so you can turn the "feature" off ASAP.

My suggestion is to turn on Name AutoCorrect and Logging. Make the change. Open EVERY query/form/report in your application unless you are CERTAIN they are not impacted. Then turn off Name AutoCorrect. Do not leave it on. It slows down the app and in some versions of Access causes problems with printing reports.

Keep in mind that code will not be changed nor will anything "embedded". I'm not sure about macros since I never use them. A simple example is a calculated control - MySum:A + B
If you change the name of B to C, this expression will not be updated. So there's lots of downside to using Name AutoCorrect but if it is all you have, it will do some of the work.

A third-party add-in would be a better choice.

 

[mgibson]

I would like to change the name of this query for the reason sxschech points out. It was originally designed for one purpose but has evolved to the point where it's name is no longer relevant and becomes confusing when other designers are adding functionality to the database.

Is there anyway to do this without installing 3rd party software?

Not sure copying the query and saving it with a new name would help as all the other queries would still reference the original name/query.

It was a little misleading to say this query is the "backbone" of my database, rather I meant to say it is used to group and manipulate information from multiple tables. This new "group" of information happens to be very useful and is used in many other queries and forms.

Is there a way to do a find and replace within access? I thought about viewing each DB object in SQL view and doing a find and replace but access doesn't allow me.

Any other ideas?

I appreciate the help

 

[Pat Hartman]

Did you read ANY of post #5 except the last line?