I highly recommend Rick Fisher’s Find and Replace utility. I’ve got both the A97 and A2K versions and is definitely worth the $29. Rick has also been very helpful in complex F&R type stuff that is not in the help manual. (I’m a professional developer and am not affiliated with RF except as a happy customer)
agree with BF. There are other find and replace utilities for Access out there, but for the money, nobody beats Rick Fisher’s utility for the money, especially for support. (No, I’m not affiliated with Rick Fisher :). I’m a project manager at a software company).
Access 2000 and XP have “Name AutoCorrect” but it’s not very powerful. From the Help menu:
On the Tools menu, click Options.
On the General tab, do any of the following:
To have Microsoft Access maintain the information it needs to run Name AutoCorrect, but not take any action, select the Track name AutoCorrect info check box.
To have Access run Name AutoCorrect as name changes occur in the database, select the Perform name AutoCorrect check box.
To have Access keep a log of the changes it makes each time it runs Name AutoCorrect, select the Log name AutoCorrect changes check box. Access saves the changes in a table called Name AutoCorrect Log. Each change is a record in the Name AutoCorrect Log table.
Dim db As Database
Dim tbl As TableDef
Set db = CurrentDb()
For Each tbl In db.TableDefs
If tbl.Name Like "Johnston*" Then
tbl.Name = "Banana" & Mid(tbl.Name, 9)
End If
Next
This renames the tables (and the same can be done for other objects, but that is only a tiny part of the problem - you must also scrutinise the SQL code for each query and replace @johnston’ with ‘Banana’ (except in cases where it appears as something other than an object name), and so on; by the time you get to sorting out the forms, you’ve got a real headache because the objects on a form reference fields in tables, your code might also - eesh! what a nightmare!
Before I composed this post, I was thinking that the Find & Replace utility (which I’ve never tried) was a rip off, but in light of my recent train of thought, I now think it is actually well worth the spend.
As for the modules (which includes the form modules and report modules), I thought you could just do a global search and replace. Isn’t there a check for “all open modules”? Just open them all up…
As for the forms themselves (which includes the reports), you can do the For Each loop on the controls on each. Pseudo-code:
For Each frm In Forms
For Each ctl in frm.Controls
If Instr(ctl.Source,"Johnson") > 0 Then
ctl.Source = "Generic"
End If
Next
Next
As regards to the queries, jbird’s approach won’t cut it. You need to change the actual SQL string. Use the InStr() function to identify occurrences of the string you want removed, and parse in the string you want to replace it with.
If you need help, post again and I’ll write a function for you.
You do have to be just a little careful with the SQl, in case there is something like:
SELECT JohnstonTable.field1
FROM JohnstonTable
WHERE (((JohnstonTable.field1) Like "Johnston"));
Will change the criteria expression too, which may not be desired, although in reality, the likelihood of such a criterion being hard-coded is quite small.