global search and replace

[DataMiner]

Hi,
My company has renamed a product. I want to find all the records in all the tables in my database that might have the old name, and replace with the new name. What's the easiest way to do this?

I don't really want to have to manually open each table and do a find.

I can write code to go through all the tabledefs and do a find on each one, but the only way I know to do that is by telling it the field name, and field names where the product name might exist vary.

I suppose I can tell it something like
for each TD in tabledefs
for each F in td.fields
{search and replace}
next f
next t

Is this really the best way? I have a program called SpeedFerret, which I thought would do this, but it apparently only searches the table names, not the actual records in the table.

 

[KeithG]

What about an update query?

 

[DataMiner]

How would I do this for all the tables in my db???? Wouldn't I have to do a separate query for each table? Plus, I'd still have to specify the field,wouldn't I?

 

[reginab]

Find and Replace Tool

There is a great tool you can get that will help you with this project. I use it all the time and is recommended by Microsoft. The name of the tool is find and replace and it is shareware and I guess you can use it without paying but I would never do that because it's a great tool. This tool will be able to rename all your tables in forms, queries and code.

http://www.rickworld.com/

If you download, let me know if you liked it.

 

[boblarson]

And that right there is a lesson on good database design. Your product name should ONLY be in ONE place in the database and it's key should be in all other tables. If you update the name in the one place, then it still works as it should. That is one, of many, good reasons to normalize your data.