Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating incorrect Table Names (Name autocorrect) 2

Status
Not open for further replies.
rj51cxa

rj51cxa

Technical User
Mar 16, 2006
216
GB
When I created my first database I was woefully ignorant of the Access naming conventions. Hence I now have tables such a "Master Address List" which have spaces between the words. As I have learned more and developed the database, the names of the objects have been entered correctly.

I would now like to be able to re-name the incorrect objects but I am nervous of doing so because the new names will not migrate through to the queries, forms, reports and VB coding.

I thought that the "Track Name AutoCorrect" function was supposed to do this but, from what I have read, it does not, and causes more problems that it resolves. The general feeling is that this option should be turned off.

Is there any guaranteed method of doing this, or am I best leaving things as they are? (If it ain't broke - don't fix it!)

Best Regards
John
 
Sort by date Sort by votes
It depends on how many tables you have and how much time you have. I would be inclined to fix it if it was possible, because I think it will save time later on. It is possible to do quite a bit with code and system tables.
 
Upvote 0 Downvote
Thanks Remou,

I understand what you are saying but I'm not sure what you mean by "system tables".

I have 31 tables, so it looks like being a job to progress over a period of time. I guess that one would need to carry out some type of search through the database to find and replace the name of the table with the new one, rather like Microsoft Word Search and Replace. If this is the method, I would appreciate some advice on the code to use.

Best Regards
John
 
Upvote 0 Downvote
The system tables are essential to the proper running of the database and should not be tampered with, however, you can use them to find where the table name is being used. In particular, look at MsysObjects and MsysQueries. You can search through the code modules, too.
 
Upvote 0 Downvote
Thanks Remou, I'll take a look and get back to you next week.

Best Regards
John
 
Upvote 0 Downvote
Hi Remou,

I've had a look at MsysObjects and MsysQueries but I cannot see how they help. I think I have worked out how to find and replace names in the VB Coding but my main problem is finding the queries that contain references to a particular table. Is there a method for doing this?

Once I have found them, I think I need to make a copy of the original table and give that the new name. Then I add this table to the query and go through, field by field, changing the name of the table. Once I have done that, I can delete the old table from the query. Seems a real slog! Is there a more straightforward method that this?

I found an article in the help section - "Customise the table and query names in a query (MDB)". I think this might be what I am looking for but it refers to a second copy of the field list. I cannot work out what this means. It goes on to talk about Alias but I'm not sure how that part of it works.

I really would appreciate a little more help if you can find the time.

Best Regards
John
 
Upvote 0 Downvote
Here is an example of using the system tables to get information:

Code: 
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysQueries 
INNER JOIN MSysObjects 
ON MSysQueries.ObjectId = MSysObjects.Id
WHERE MSysQueries.Expression Like "*" & [Enter table name: ] & "*"

Do not change the system tables, however, it will seriously damage your database.


 
Upvote 0 Downvote
Thanks Remou, I'll take another look. I have a feeling this is getting too deep for me but I'll keep trying - its the only way to learn.

Zameer,

Thanks for the tip, I'll take a look.

Best Regards
John
 
Upvote 0 Downvote
You could start with one peripheral table and see how it feels. It is quite interesting.
 
Upvote 0 Downvote
Wow, such a lot of information. It will take me a while to sort it out. I'll respond when I've had a good look.

Thanks to all
John
 
Upvote 0 Downvote
Remou,

I've run the query as you suggested and have come up with a most interesting list of data. It shows me all the references to a particular table in all the queries and reports but it would now seem that I need to take each reference and rename it with the correct table name. However, before I start this very laborious process, is there any way of using a query to change the name in each of the references?

Also, I need to change the name of queries and reports to comply with the correct naming convention. Can I change the query to select queries or reports instead of tables?

Best Regards
John
 
Upvote 0 Downvote
Please don't change or fiddle with the system tables, terrible things will happen to your database. And I mean that with red highlights and bells.

I will get back with more information on speeding things up.
 
Upvote 0 Downvote
The table MSysObjects contains, amongst other information, the name and type of objects stored in the database. here is a list of types, it is not exhaustive, but should be sufficient for your purposes:

[tt]1 Table
5 Query
Note that query names that start with ~SQL are
queries stored in tables, reports, controls, etc.
6 Attached Table
-32768 Form
-32764 Report
-32761 Module[/tt]

The table MSysQueries stores further information on the tables and queries used to make up queries. These two tables should show you how much there is to do.

It is not difficult to iterate through the forms and reports used in your database and check the table names used in the record source for the form and rowsource and control source for the controls, however there are a number of other places a table name might turn up such as in a filter, which is why, hopefully your forms and reports are based on queries. It is also possible to update these properties via code.

I have some code that will list all the properties for each control in every form, but is by no means neat. You may also wish to look at:
A particularly useful documenter.
 
Upvote 0 Downvote
Thanks for the warning, Remou, I'll leave well alone. I'll go on changing such things as Report and Form names but leave the tables until last.

I tried ZmrAbdulla's suggestion about using NJH Tools but I don't think this will do any more than Remou's suggestion.

Best Regards
John
 
Upvote 0 Downvote
I am sure NJH tools will do this easily because you will do it as normal find/replace in a UI.

Also this is an interesting addin

Not remembering this also will... But a useful addin for sure


________________________________________________________
Zameer Abdulla
Help to find Missing people
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

A
  • Question
Print Table attachment
Replies
2
Views
134
andre.meyer
A
OCM
  • Locked
  • Question
Populate access table from excel
Replies
6
Views
3K
OCM
OCM
renigar
  • Locked
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
677
renigar
renigar
TheresAlwaysAWay
  • Locked
  • Question
Long-established database suddenly giving error Cannot open macro [Name] because it is not valid
Replies
6
Views
575
dhookom
dhookom
Moss100
  • Locked
  • Question
Using data macro to alert user when record is added to a table.
Replies
2
Views
212
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top