MS Access Question: Global Name Change Possible?

I created a very nice database, and now I want to use it to make other databases for other people/places.

However, as it was my first major Access Database, I stupidly named everything after the case/company.

What I mean is, in this database I now have the JohnstonTable, the JohnstonQuery, the JohnstonReport, the JohnstonFax…

Getting the idea?

I want to either remove ALL references to Johnston, or replace it with XY or whatever.

The problem is, if I don’t change ALL references, then you know things are going to get screwy, fast.

Do I have to go in and change each one individually?
Or is there a global search and replace on MS Access?

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

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.

In our shop we use it all the time. I haven’t tried any of the other utilities, but it’s a life saver.

Do the same, except with QueryDef and qry.SQL.

Find and replace in the VB code, and then all you’ll have left is form and report properties (which would be easy to find anyway).

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

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.

[fixed vBulletin tags]

I downloaded the program from rickworld and WOW!

It would have taken me weeks and lots of headaches to do what that little program did in less than a couple of minutes!

Thank you guys for the suggestion!!!