Table/Query name change updater

[wallis1905]

Hello all,

Hope somebody can help with this. I am currently trying to tidy up a large messy database. I am standardising the naming convention for tables and queries etc, as they are a mess. I want to create a form with some code perhaps, that will allow me to change the name of a query etc, and automatically update the name change into any dependant queries, Reports, Lookups.
So far I have managed to do just this for Queries, but not the others. I can select an entity from a combo box, and enter its new name in a text box. I then find and replace all sql statements swaping the old name with the new name.
Anybody know how to drill into the rowsource property of a report, or lookup( in a table) and find replace that string?Preferably without opening each table,/Report to check rowsources. This must have been done before but cant seem to find it. Pretty useful tool if possible.
Any help is greatly appreciated.

[OBP]

wallis, I hope that your Database does not use very much VBA Code, because if it does you are in for a great deal of work replacing every instance of those names in the code.
You can use the varuous "Collections" to change names etc and probably use the Collection's Definitions to change their data sources as well.
If you open the VBA Editor's Help, (which is different to the standard Access help), you can type "Collection" in to the Help Search and it will show you the various types of collections. Check AllForms for the "Forms" collection and what you can do with it as far as it's controls are concerned.

[wallis1905]

Ok OPB
I'll try that. Dont have a lot of code anyway. And any modules I have, pull the query name and table names in at run time so I think that wont be a problem. Is this what you mean?

Thanks a lot

[OBP]

Don't forget Command Buttons or Switchboards that open forms etc.

[wallis1905]

Yeah, we don't use any of that stuff.All my command buttons are simply run code buttons. Besides generally the names have a numerical prefix that need not be changed, the following descriptive text those. I could use the prefix to identify reports etc in the command buttons code?

[OBP]

I am not sure I would have to see the format to make a comment.

[wallis1905]

This seems to work for table lookup rowsource
Dim MyRst As DAO.Recordset
Dim strSQL, strA, strB As String
Dim MyDbs As DAO.Database
Dim str As Variant
str = DBEngine(0)(0).TableDefs("Design_Index").Fields("Chan").Properties("RowSource")
str = Replace(str, "Channel_Select", "Component_List")
DBEngine(0)(0).TableDefs("Design_Index").Fields("Chan").Properties("RowSource") = str
Here I have types in the old and new name. These will come from a combo box and a listbox respectivley in practise.
Works on a closed table too. Now just the forms an I'm done. Ive read up Allforms. Apparently that is read only so I persume I cant use that.

[DomFino]

Hi, what you may want to do is use:Find and Replace
http://www.rickworld.com/products.html

This tool can be downloaded at the link above and used for 30 days free. I just went through a major clean-up and this product did the trick.