Boy, where to begin. Here are some things that have caused me headaches in
the past:
If you change table names and then go into the design view of a query that
has multiple linked tables, it will pretty much barf before opening with
very little left to see. This makes reconstructing things way more
difficult. I used to create the new tables first, drag them into the
not-yet-modified query, and copy things while they're still visible.
Remember to look for the old table/form/query names in other queries, in
form and report data sources, VB code, macros, any established
relationships, to name a few.
If you have a split database, you'll have to roll out the new GUI at the
same time as the new back-end tables, or existing GUIs will barf on
themselves, or possibly corrupt data in the new tables.
That's what comes to mind right off, there's probably more. I used to use
Speed Ferret to do massive search-and-replace operations. That made it
mostly painless, but there were still snags that had to be fixed by hand,
timing issues on what should be changed before or after what, etc. I'm
always coming up with "a better way" to name things myself (sometimes the
really right way to do things, other times just on a whim), so I feel your
pain.
Good luck!
Armando