renaming Naming the Seven Access Object Types

[smile]

When you are working with the seven different object types that make up Microsoft Access, each of the objects has its own prefixes. A list of those prefix names you should be using for the Access Object Types are shown below -

db - Database
tbl - Tables
qry - Queries
frm - Forms
rpt - Reports
mcr - Macros
mdl - Modules

In my database I have not added tbl_ to my tables and would like to do so.
What problems can I have? Won't any queries get broken etc.

I searched the forum but could not find any rename policy suggestions etc.

 

[RuralGuy]

You can have quite a few problems after renaming those objects. I would put off the task until your *next* project.

 

[smile]

You can have quite a few problems after renaming those objects. I would put off the task until your *next* project.

But in practice access tracks all objects by itself, only formulas can have a problem no?

So reports can be renamed without any problems because they are the last objects in a chain.

Tables are the first and can have some problems as queries too

I hoped there would be a guide or recommendations like:
1. It's safe to rename reports
2. If you rename tables your queries can malfunction, check this and that etc.

 

[stopher]

I hoped there would be a guide or recommendations like:
1. It's safe to rename reports
2. If you rename tables your queries can malfunction, check this and that etc.

"if it works, don't fix it". As Ruralguy says, there's no problem leaving it as it is an using the convention in your next project.

However, if it is really irritating you then I would take the "suck it and see" appoach:

1. MAKE A BACKUP of your database

2. In the relationships view, delete all the relationships (don't remove the tables from the view). Save the relationship view.

3. Rename all you objects !!!

4. In the relationships view, re-create all the relationships. Save the relationship view.

5. Open each query, one by one i.e. test that it works. I think later versions on Access will pick up the new table names in the queries. However, you may find that some queries have lost the plot and you have to change your table references. Forumulas should generally still be fine except where you have had to specify table names e.g. if you have used a field that has the same name in more than one table.

6. Test each form, report, macro, module etc. In my experience they will either work or they won't. So it's just a case of correcting everything as you go. Obviously you will have to change the record source of forms and reports. If you have done a lot of custom work on your forms then this is where you might have some heartache. But the problems should be obvious and therefore just a case of wading through.

This approach might seem un-systematic but I've got away with it in the past.

Don't forget to take a backup!!!!!

Chris

 

[RuralGuy]

Because of the Failures caused by Name AutoCorrect, most developers turn it off first thing. Good luck.

 

[gemma-the-husky]

if you rename a table, then any qwuery using the original table name, or any code looking for the original table, will no longer find it

i think there are third party tools that will help on this, but otherwise its better not to try to rename objects used in other objects - ie rename forms and reports, but not tables and queries

 

[boblarson]

You can try this on a copy -

Download the free V-Tools
http://www.skrol29.com/dev/en_vtools.htm

And then use the Total Deep Search tool to rename. It should rename it EVERYWHERE it is used. But, like I said, do it on a COPY of the database so that if something gets honked up, you won't be in trouble. AND disable Name AutoCorrect from Tools > Options > General tab

 

[smile]

Thanks everyone, I'll try the program and post back.

 

[Pat Hartman]

Actually, reading the writeup on Name Autocorrect in the KB explains a lot of its strange behavior. I do use it in a limited way and would try it in this case. You will need to turn it on (leaving it off normally is the best way to use it), make the change, then turn it off immediately so you won't forget. Your queries and forms should get updated. but test everything to be sure. You will need to change and code references yourself and I'm not sure about macros since I never use them.

 

[smile]

I use Access 2007 and my databse is old 2003 format mdb.

1. I made a backup copy of my database file.
2. I left Name AutoCorrect ON
3. I started renaming my reports by adding rpt_
4. Tested if they work - They DO

5. Started to rename tables adding tbl_
6. Tested if they work by opening queries and reports - They DO

Minor problem was encountered while opened direct query to one of my tables that had only that table fields and table had lookup fields from another table.

Problem was that I so not field names but ID numbers.

I created new query to see if it would work. To my surprise it worked. Then I opened old query - IT WORKED TOO

7. Tested every report to make sure they work.
8. Opened relationship windows, everything is gone.
9. Added "Add All" and rearranged like it used to be.

10. Tested everything: reports - worked; Queries - worked;

My relationships were all "lookup" type. SO I guess they renamed automatically.

I open for suggestions on testing to make sure it works, could I create a dump of some kind and compare to unrenamed database?

 

[Pat Hartman]

As long as you opened every object and didn't get errors regarding the name, you're all set. Now that your change has been made, you should probably turn off the Name Autocorrect. It slows down the application and it does have some unexpected consequences which are explained very well in the document in the KB.