changing naming schema

[Prayder]

I have taken over exisiting databases and the names for the tables,forms,queries,reports are not technically named correctly.. What would be the easiest way to rename everything accordingly:

tbl for table
qry for query
rpt for report
mcr for macro
bas for module
frm for form.

Of Course they have names but it is hard to tell when viewing a query for instance if you are looking at a table or a query.... in the SQL View.

 

[spikepl]

Existing working db's? Do not touch them! If you rename everything you'll spend unpredictable amounts of time on making stuff work, and what you have at the end of the day are the same databases. How much do you wish to spend on nothing?

For naming convention for new db's see here: http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[Prayder]

Ok.... I figured I would ask but had already decided to leave them alone... why fix something when it is not broken...

 

[NigelShaw]

well, you 'could' do it at your own peril.....

using vba, you can loop through the forms, queries and tables opening them in design view and change the name that way, then try and sift through the code to change the same with a find/replace?

N

 

[Galaxiom]

I have taken over exisiting databases and the names for the tables,forms,queries,reports are not technically named correctly.

It isn't about being correct.

While some developers treat it as gospel, the use of these prefixes in object and variable names is highly overrated in my opinion. Many developers don't use it.

Indeed there are circumstances where these prefixes make no sense and actually mislead anyone subsequently working on the database.

It is certainly not worth the trouble to rename them in a working database.

 

[NigelShaw]

It isn't about being correct.

While some developers treat it as gospel, the use of these prefixes in object and variable names is highly overrated in my opinion. Many developers don't use it.

Indeed there are circumstances where these prefixes make no sense and actually mislead anyone subsequently working on the database.

It is certainly not worth the trouble to rename them in a working database.

I would agree and disagree. It's not gospel, you're totally right. It's more a programming preference. The only reason I keep to a standard of sorts is to stop me getting confused. When looking through reams of coding, it's to remember whether your record set is collecting from a table or query. I keep them named in a similar fashion so I can see at a glance. By naming the type (tbl / qry) it helps me. It's certainly not done because 'that's the way'


Thanks

 

[RainLover]

Somewhere in the back of my mind I remember an option that when a Table is renamed it is renamed in all places where that table is used.

Same for queries and I think Fields.

Perhaps this goes back to Access 97. Not sure.

Can anyone support this. In fact the more I think about it I am sure there is an option somewhere but I can't think of where.

 

[RainLover]

Found it.

In options under the General Tab.

Name AutoCorrect. Tick Perform name AutoCorrect.

 

[bob fitz]

Found it.

In options under the General Tab.

Name AutoCorrect. Tick Perform name AutoCorrect.

Hi Rain,

I could be wrong (I often am ) but I'm pretty sure that I've read something about "AutoCorrect" being the cause of corruption.

 

[bob fitz]

Regarding my last post, see: http://allenbrowne.com/bug-03.html

 

[spikepl]

@bob fitz

So you think that a 10+year-old bug still flourishes? My bet is that this is one of the outdated "universal truths" propagated by the guru community.

 

[RainLover]

Bob

Do you subscribe to the notion that if it is said by a MVP (Especially Allen Browne) then it must be correct.

I certainly do not. And BTW I don't think Allen Browne is even a MVP but not sure.

I had completely forgotten about this feature so I have had to refresh my mind.

Correct me if I am wrong, but is not this option the default. Again not 100% sure.

My suggestion was originally intended to allow the OP to make the changes he wanted without causing the system to fall apart. In other words if it is off then turn it on make a change or two, test and if all is OK then change a bit more. Again relying on memory, it is important to open any dependant object and not view it in design. (Only required once) otherwise the autocorrect does not work. Once finished the user can turn the feature off or leave it on dependant on what they want.

A bit more for you to think about. If it is the default which is what I suspect, then every Database I have written since I moved on from 97 has been written with this feature turned on. Two or three of these databases I wrote were developed of a few years. I have never had a single complaint like those listed by Allen. One of these database was written over 2 years or more in Access 2002.

I am not saying here that anyone is wrong. I am simple saying that in my experience with Databases that have been running near on ten years I have not had a problem.

As soon as I can put aside some time I will look closer at Allen's claims.

I may come back with additional comments.

EDIT

Name Auto Correct must be ON when creating the object. If not then the NAC does not no it exists therefore it cannot make a change.

This was pointed out in a later post within this thread.

 

[RainLover]

@bob fitz

So you think that a 10+year-old bug still flourishes? My bet is that this is one of the outdated "universal truths" propagated by the guru community.

Mate

You have a way with words.

You have stated the obvious.

 

[bob fitz]

@bob fitz

So you think that a 10+year-old bug still flourishes? My bet is that this is one of the outdated "universal truths" propagated by the guru community.

And you may well be correct. I have no idea. I do not remember reading that suggestion anywhere else. If I had I would not have made the post that I did. I'm happy for those with more knowledge and experience to pass judgement on it.

 

[bob fitz]

Bob

Do you subscribe to the notion that if it is said by a MVP (Especially Allen Browne) then it must be correct.

I certainly do not. And BTW I don't think Allen Browne is even a MVP but not sure.

I had completely forgotten about this feature so I have had to refresh my mind.

Correct me if I am wrong, but is not this option the default. Again not 100% sure.

My suggestion was originally intended to allow the OP to make the changes he wanted without causing the system to fall apart. In other words if it is off then turn it on make a change or two, test and if all is OK then change a bit more. Again relying on memory, it is important to open any dependant object and not view it in design. (Only required once) otherwise the autocorrect does not work. Once finished the user can turn the feature off or leave it on dependant on what they want.

A bit more for you to think about. If it is the default which is what I suspect, then every Database I have written since I moved on from 97 has been written with this feature turned on. Two or three of these databases I wrote were developed of a few years. I have never had a single complaint like those listed by Allen. One of these database was written over 2 years or more in Access 2002.

I am not saying here that anyone is wrong. I am simple saying that in my experience with Databases that have been running near on ten years I have not had a problem.

As soon as I can put aside some time I will look closer at Allen's claims.

I may come back with additional comments.

If I have spoken "out of turn" or caused any offence then I am truly sorry. I was only trying to help.

 

[Galaxiom]

Name Auto Correct records the change then updates it in the related objects when they are opened.

However NAC doesn't change everything. Code, SQL text rowsources and recordsources are not processed.

I have personally experienced problems with NAC. I am pretty sure it was with Access 2007. However rather than throw out the baby with the bathwater as many developers have done when they encountered a problem, I persisted and found the cause.

Unfortunately I didn't take notes but I had renamed a table to the same name as a field. Somehow NAC appeared to have confused the two in a query even though the query design and SQL looked fine. I don't remember the error message exactly but I think it started saying that a table couldn't be found but it failed to report the name of the table.

I have also seen queries corrupted after a field is renamed such that they ask for a parameter named with the old field name. This is despite the query showing the new name in both the designer and the SQL.

In these cases the corruption is easily fixed by copying the SQL to a new query.

Clearly Access actually stores queries in a format that is neither SQL nor the designer and then interprets it for display.

 

[gemma-the-husky]

Name Auto Correct records the change then updates it in the related objects when they are opened.

However NAC doesn't change everything. Code, SQL text rowsources and recordsources are not processed.

I have personally experienced problems with NAC. I am pretty sure it was with Access 2007. However rather than throw out the baby with the bathwater as many developers have done when they encountered a problem, I persisted and found the cause.

Unfortunately I didn't take notes but I had renamed a table to the same name as a field. Somehow NAC appeared to have confused the two in a query even though the query design and SQL looked fine. I don't remember the error message exactly but I think it started saying that a table couldn't be found but it failed to report the name of the table.

I have also seen queries corrupted after a field is renamed such that they ask for a parameter named with the old field name. This is despite the query showing the new name in both the designer and the SQL.

In these cases the corruption is easily fixed by copying the SQL to a new query.

Clearly Access actually stores queries in a format that is neither SQL nor the designer and then interprets it for display.

there are a few problems with renaming stuff.

among others, a couple of obvious ones
- one is that you may rename it in a way that causes some unwanted side effects with existing code.
- or you may rename a field to have the same name as a field in another table - whereupon queries can fail because the query cannot now determine which of the two fields you are referring to in some related issue.

the bigger the dbs, the more likely renaming is to cause a problem, I tihnk.

 

[Galaxiom]

- one is that you may rename it in a way that causes some unwanted side effects with existing code.
- or you may rename a field to have the same name as a field in another table - whereupon queries can fail because the query cannot now determine which of the two fields you are referring to in some related issue.

Yes these are certainly potential problems with any automated rename.

Another that I suspect is very common is copying an object than renaming the original as a "backup" with NAC on. NAC is on by default and it can catch developers unaware of its behaviour.

NAC propogates the change then they rename the copy to the original name and wonder why the queries are broken.

However I know from my own experience that real corruption also can happen with NAC.

It hasn't put me off using it occasionally but I take great care to consider the names I assign so that I don't need to change them.

Also I generally plural words for tablenames and singular words for fields so that it avoids any chance of hitting the same name. In most cases fields are named the same in different tables only where they are related.

 

[RainLover]

If I have spoken "out of turn" or caused any offence then I am truly sorry. I was only trying to help.

Bob

What is this about?

How could you have possibly offend anyone over your statement. You are entitled to your opinion and you simply expressed it.

I was more offensive that you and no way I am going to apologise. I have had enough of people quoting MVPs as though they were Gods. They are anything but.

What an MVP is, is someone who has answered Microsoft's phone more often than others. I met a few of them at a Microsoft convention a few years back. And fair dinkum you would not feed half of them.

I prefer to hear about people personal experiences and how they overcome their particular problem. Much more credible.

Bob, don't be a blouse and just say what you think. Besides in this case you did raise a good point.

Have a good one.

 

[Pat Hartman]

As a general rule, I leave Name Auto Correct turned off. Even if the corruption problems have been corrected, it still adds significant overhead to your development process and it doesn't work the way you think it does. However, when I need to make bulk changes and I want the help, I turn it on. But, before you do that, make sure to read carefully the attached document because as I said, it doesn't work the way you think it does. In particular, changes are not actually propagated until the target object is opened. So if you change a column name, the change is logged but nothing else changes. Only if you open each form/report/query/macro will the change be propagated. Where people get into trouble (and I'm sure NAC gets confused) is when there are layers of changes to apply. So, I make changes to one table at a time and open all objects that use the table. Then go back and do the next one. The doc contains a link to where I got it originally.

To make this work for you, you will need to go back to a one file .accdb by importing back all the linked tables. That is the only way Access can propagate changes. You can't change linked tables from the FE and when you change them in the BE, Access has no clue what FE should be changed. So - merge them back. It's a lot of trouble to go through so make sure it will be worth your time. If the BE is SQL Server, the problem is even worse because importing the tables one at a time won't import the relationships and PKs and Indexes, you will need to recreate all of them.

If your changes are sporadic, don't bother with NAC. Make the changes to the BE. Then open EVERY FE object and fix the errors as they are reported. And in any case, NAC cannot change code so you are on your own there. It also doesn't change calculated fields in queries so you'll have to ferret those out yourself. There is a global change product that does a better job but you still have the BE/FE issue.