Find and replace table/field names in Access?

Question

I've been handed a rather large, old, and crusty Access 2000/2003 database to clean up. I'll need to rename a number of tables and fields, but need to do so in a way that doesn't break existing queries. (Fortunately, there are no macros, forms, or reports to worry about.)

I've heard there are some commercial tools to make this job easier, but only found one (http://www.rickworld.com/products.html).

Can anyone recommend this software, or any alternatives?

Solution 1

Name AutoCorrect is something I use only when I'm consciously renaming tables/fields/Access objects. When I'm done with the "great renaming" I turn it off.

For a recent project, I did that but also knocked together a little utility so I could transfer my renaming from my working database to the production database. The utility does some other things as well (such as copying relationships and deleting lookup fields). You can download it from my website's Access downloads page. It's very rough and with very little error trapping, so use at your own risk.

For the most recent project, I used Name AutoCorrect, the database above, and then Rick Fisher's Find & Replace and a very useful but no longer available Find/Replace utility called MDBSearch, credited to "Ucora and Jamie Hornstein." I don't even know where I got it, and can no longer locate it on the web. It may be available somewhere I didn't look.

I've used Black Moshannon's SpeedFerret in the past, and it's a superb product (the slickest and fullest-featured of any Access Find/Replace utility I've seen) but they've not kept up with newer versions of Access, and I see no evidence that a new version is coming out. Too bad, because at $99, it more than paid for itself within the first couple of hours of paid work.

Solution 2

This may be a case for Name Autocorrect: https://support.office.com/en-ie/article/Set-name-AutoCorrect-options-b475af37-dcf8-477e-a9d8-32ca9c1d4623

Name AutoCorrect is a feature that helps keep the functionality of your database from breaking when its design is frequently changed, especially if several users make unplanned design changes. By default, name AutoCorrect is enabled. However, name AutoCorrect does slightly impede system performance, and you might want to turn it off if the database design is not subject to unplanned design changes.

Name AutoCorrect helps keep the names of database objects synchronized. Other potential design change problems, such as problems arising from deleted database objects, are not directly handled by name AutoCorrect.

See also http://allenbrowne.com/bug-03.htmln Failures caused by Name AutoCorrect

Comments

[Fear605]

Great! But it wouldn't do two things I still need: (1) finding all instances of certain text, to help me figure out the dependencies (which tables/fields I can remove), and (2) making some en masse changes, such as removing "dt_" from the beginning of every table name without changing each one individually.

[David-W-Fenton]

While Name AutoCorrect was designed for this purpose, most Access developers quickly discovered after its introduction that it was a performance hog and could very easily corrupt a database. Because of that, I only use it for a global renaming project and turn it off as soon as the renaming is done.

[Steve]

Does this answer still apply for Access 2007 - 2010? (I see from the site that it seems to, but I'm wondering whether the new version is as much-recommended as the older versions.)

[Sandi Laufenberg-Deku]

@Fionnuala - that is fine and dandy - and thanks for editing the post -so that the link works. I will now delete my comment.