Find and replace table name

[jpl458]

I have an app the extensively uses the name of a table. I want to change the name of that table wherever it appears in the app. I have made a back up of the app, but when I go into Find and Replace in the code window it only works on the form that is open. Is there a way to change it everywhere in the app?

 

[Minty]

V-Tools has a deep search and find
Skrol - V Tools

 

[CJ_London]

but when I go into Find and Replace in the code window it only works on the form that is open.

Did you select current project in the find window?

 

[moke123]

select current project

MZ Tools has a better one

 

[jpl458]

Yes, and then Find Next get grayed out.

 

[jpl458]

Yes, and then Find Next get grayed out.

Selected Current Project but it says it cant fined what I am looking for. Its the name of a table that I want to change.

I'm no expert in ACCESS, but it seems to me that you should be able to change something everywhere in the project. I can do that in Python IDE.

 

[jpl458]

Selected Current Project but it says it cant fined what I am looking for. Its the name of a table that I want to change. Table name is used in queries, Forms.

View attachment 104687

View attachment 104688

I'm no expert in ACCESS, but it seems to me that you should be able to change something everywhere in the project. I can do that in Python IDE.

 

[Pat Hartman]

Find and Replace only works in VBA. It does not change macros or property setting. Are you certain that once you changed the setting correctly to Current Project that not all instances were changed?

 

[jpl458]

Absolutely sure. The name I want to change appears in queries and as the Record Source in several forms. Do I need to get a 3rd party somthing-or-other to do a global change to a project? But I think we talked about this and you told me to make sure I had a backup before doing it. That led me to believe that you had done the same thing at some time or other. I do it in the code window, but that only shows the code for the open form in design mode. Am I doing something wrong here? Else, I make the change and do regression testing and make all the changes by hand. That really sucks.

Just added this after thinking. If it works, would it change the name in the SQL for queries?

 

[Minty]

The short answer is no - you need a third party add-in like V-Tools

It goes through all the table defs, query defs, and code and finds all the instances of the search string.

 

[ebs17]

Simple way: Create a query and name it OldTableName.

SELECT * FROM NewTableName

A little more comprehensive, but without claiming a third-party solution: Export your objects with SaveAsText. Text files are created with the complete definitions. In these text files you can replace the table name. Then the object definitions are re-imported with LoadFromText.

 

[Pat Hartman]

Whenever you do a global find and replace, you should make a backup. However, I assumed you knew that the find and replace in VBA worked ONLY ON VBA.

I'm not sure that the MZ tools works on all the other objects.

Access does have an additional feature which is very dangerous and so experts usually recommend that it be turned off unless you specifically want to use it and that is the Name Auto Correct feature. When you turn it on, make sure to turn on the log also so you can see what has been changed.

Pay close attention and make several backups. At least one should be zipped-
The table whose name you want to change is almost certainly linked. If it is, delete the link and then import the definition from the BE. Do NOT import the data.
1. Turn on the Object Dependencies by pushing the button

2. This is what you see:

As you click on each table/query/form/report, the list at the right will change. You can look at the dependencies two ways. Objects that depend on me and Objects that I depend on. This will give you an over view of what Access will actually change. REMEMBER, it does NOT change every reference. For that you need a good Find & Replace too.

3. Start by changing the table name since that is what you want to change. Then, one by one - and this is CRITICAL to avoid the problems associated with this "feature", open each object that you think got changed. Use an old version to keep track of how the list looked originally so you make sure to open EVERYTHING. The reason for this is because Access does NOT propagate the change at the time you make it. It makes a note to itself that it needs to but it doesn't do it immediately. It only makes the change the next time you open an object that needs to be changed. This is where people get into trouble. If they make multiple changes to the same object without opening it, the changes can get confused.

4. Don't forget to delete the local, empty table and relink to your actual table in the BE.

5. Once you are sure everything works, turn off the Name Auto Correct and make a new backup. Leaving it on is dangerous. For example, if it is on and you rename an object to make a backup so you can change the new version, you will end up with that change being propagated and your form being linked to xxOldName instead of the new version of OldName.

 

[CJ_London]

The name I want to change appears in queries and as the Record Source in several forms.

if you have autocorrect turned on, changing the name of a table or table field will change it in other objects (queries, forms and reports) providing a) they are closed at the time and b) you have autocorrect turned on. You use find and replace in code as you have already tried

The only exception I can think of is it won't change it in subqueries

However I see Pat has provided a more detailed response

 

[Rutillo]

When I saw the title of the topic, I was interested because I thought it was possible to do such an action directly.
However, I didn't know about such tool as V tool. Thank you for sharing!

 

[Pat Hartman]

For people who want to try using Name Auto Correct, I suggest that you read the document attached. The PPT is a summary I made when explaining the tool to a user group.