Solved

Expert Topic: Naming conventions in Access databases

Posted on 2011-02-22
31
1,348 Views
Last Modified: 2012-06-22
This discussion is intended for Experts in the Access zones, and is focused on naming conventions.

Originated from: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26827207.html

What do you use?
What do you avoid?
Why?
I'm particularly interested in noteworthy experiences in following (or not following) specific conventions.
Etc?

This thread is publically visible, so please keep comments professional (eg: no flaming or derogatory comments about other Members).  If you do have commments that should be handled offline, please contact myself, JDettman, or jimpen at our e-e.com email addresses.

mbizup
Access Zone Advisor
0
Comment
Question by:mbizup
  • 6
  • 5
  • 4
  • +7
31 Comments
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 0 total points
Comment Utility
The reason I raised this issue was not because I see some particular advantages in using the spaces, but because (1) this is allowed (2) there's no scenario where doing that would present any objective problem (and the keyword here is objective - with full understanding that there may be a lot of subjective ones) - so it's a matter of pure personal choice and personal habit. If somebody is comfortable with using the spaces and thus enclosing table names in quotes, is OK; if somebody is not, it's equally OK, not better, not worse. So my point was not in using them or not, but rather in the degree of personal choice allowed in everyone's coding.

I personally use identifiers without spaces, often very long, WithEveryWordCapitalized. But I don't feel any discomfort when I see totally different styles.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

 Good idea, this one might get long...

JimD.
0
 
LVL 18

Assisted Solution

by:jmoss111
jmoss111 earned 0 total points
Comment Utility
I use a modified Leszynski naming convention and use CamelCase, and in a years time that saves some typing. In my world there is no room for spaces in any kind of object name or in a path.
0
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
0
 
LVL 10

Assisted Solution

by:VTKegan
VTKegan earned 0 total points
Comment Utility
In response to vadim from the other thread...

The prefix I would say is necessary just in code to make it more understandable.  More in Access than in SQL, because you could have rptCustomers, frmCustomers, tblCustomers, modCustomers,qryCustomers... so in code if you just saw 'Customers' is it the form, report, query etc...

The prefix is important.

When I first started in Access which wasn't too long ago, I was unaware of the naming conventions, and I was using spaces _'s and -'s which turns out to create major headaches.

In all new applications, I use just as Jim has described.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 0 total points
Comment Utility
<<I would in fact name it Customers, because when you think about the table, it does indeed represent customers, so if it's the most intuitive, why resisting. Customer is one row, so maybe it would make sense to name a cursor (in sql server) that way :-)

One maybe interesting point is, why you need the prefix. Wouldn't not using the prefix make it more transparent, since query and table are fully interoperable (same as view and table in sql server). Sometimes, for various reasons, I even "substitute" a view for a table, i.e. I will rename the table and put view instead, so old code won't even notice.
>>

  Tables and queries are different objects and they have different properties, so I name them with a different prefix.  This is especially helpful in code.  If I see:

  tblCustomers

 vs

  qryCustomers

  I will know right away that I am dealing with something different then a straight table.

JimD.
0
 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 0 total points
Comment Utility
I haven't had any formal training in VBA/Access/SQL/RDBMS etc etc, but intuitively I knew NOT to include spaces in any abject name.

As a developer now for over 15 years, my own preference lead me towards the CapitalEveryWord naming convention, and like jmoss (and probably many others, the Leszynski/Simonyi conventions are mentioned in that Access bible we all know and love - Access Developers handbook), it works, its intuitive, makes programming, be it SQL/VB/C++ etc much much more simple than having spaces included!

I agree with vadimrapp1, its a personal preference though and if you are developing code that is going to be supported and maintained by oneself, who cares, you reap what you sow!
0
 
LVL 61

Author Comment

by:mbizup
Comment Utility
I've been following the Reddick VBA naming conventions, mainly because that is what our organization does, and that is along the lines of what everything I have read on the subject recommends.  Plus it looks better, and is easier to read and write than names that include spaces or brackets.

I have never personally had any trouble, or seen any forum issues directly attributable to the use of spaces in particular as long as brackets are used appropriately.

I have seen posts out there from other Experts, though which pinpoint through examples problems (or just weirdnesses) caused by special characters.   I'm curious whether anyone has any specific examples or links to such posts...
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 0 total points
Comment Utility
I had a developer do an Access db that kept having issues when she started coding instead of just using the Append/Update queries and macros. The db had been in use about 6 months when they tried automating it with real VBA. Her key fields were named things like "Request No" and "Part No". Can anyone see the issue?

Another case -- back in SQL 2000 SP1 -- I was using the maintenance plan wizard to build the backup plans. Some expert MCSE consultant (and I use the term loosely) was brought in to help configure our Citrix. He threw a database on the SQL server named "Citrix Apps". It broke every single one of my maint plans. The worst part -- it didn't show up in the logs as an error overall -- only in the steps. It was a month that my butt was uncovered.

I was discussing the issue with the MCSE later on. His response was "Well M$ says you can do it."  My response was "While you can do something, it doesn't mean it is a good idea." He was flabbergasted at the concept. Later on the new networking manager told his firm that we never want him on our property again. We cheered.

I had an EE question on a corrupted SQL db used to archive Exchange. He couldn't understand why he was having issues working with a database named something like "Exchange Server1 1/1/2008-12/31/2008 Archive".

I avoid spaces and special characters in all my development. The only special character I use is the underscore. My preference is to use an underscore for field_names and CamelCase for variables. And if I have a choice for an export/import delimiter it is the pipe "|".

and if a particular coder has chosen to use spaces, it's his own personal choice,

I have seen, maybe, one long term coder that uses spaces/special characters for field/variable names. The reason for that is over time they have been burned by it. Yes, in the perfect world everything should be bracketed or quoted and fully qualified.

absolutely no objective reason to criticize him for that and suggest to change his style to the one you like better

I am going to  have to disagree with that. If the app/project is developed by a single person that will recode it for the rest of his life; or the application will never have to be changed -- code that way. The reality is after that coder is gone -- the line animals (production DBAs, the networking guy who got stuck with it, the junior coders) have to live with that developers choice to code that way. Also the number of applications that don't have a version 2.x.x or 3 or 4 is vanishingly small. Just like so many other places -- software is also publish or perish.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility

  and BTW, when were done with this, VTKegan wanted to explore classes as it related to a problem:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26798403.html

  I've had this sitting in my "pending action" bucket since the start of Feb, but still haven't found any time to get to it (and this week/weekend is not looking good either).  When were done with this, I'll post it on the notify thread and see who might want to jump in.

  I've got two other extended type questions as well still waiting, although those are for memebers and one already has an accepted solution (he just wants something better now<g>).

JimD.
0
 
LVL 61

Author Comment

by:mbizup
Comment Utility
Rddick VBA conventions:
http://www.xoc.net/standards/rvbanc.asp

Access specific conventions midway down the page.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I will go the other way on naming tables/forms/views etc. I want it as a suffix not a prefix. If you coding with intellisense -- or even just trying to find the object in the GUI -- have every table prefixed with "tbl" makes it worthless.

Tell me how to quickly it is to find tblWorkRequests among 200 other tables in the GUI? Where if is WorkRequestsTbl you can tap the "W" to get to the general area. Same with any other object.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 0 total points
Comment Utility
> If the app/project is developed by a single person that will recode it for the rest of his life; or the application will never have to be changed -- code that way. The reality is after that coder is gone -- the line animals (production DBAs, the networking guy who got stuck with it, the junior coders) have to live with that developers choice to code that way. Also the number of applications that don't have a version 2.x.x or 3 or 4 is vanishingly small. Just like so many other places -- software is also publish or perish.


Makes perfect sense. Though, I'd expect that in real software production environment, the number of various standards on coding would be much more extensive, and mandatory to follow. Including things like dimensions of the buttons, their positioning, and 100 others.

If I understand correctly, the examples you gave, when things really broke because of spaces in the names, were because there was some code that was dynamically scrolling through all available objects in the database, and that code did not account for the possibility of space, i.e. it had something like

sql = "select something from " & tblName & " where ...

rather than

sql = "select something from """ & tblName & """ where ...

Right?
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 0 total points
Comment Utility
* Limit table/query/feield names to upper-case and lower-case alphabetic,  numeric digits, and underscore character.  That means no spaces and no punctuation/special characters.
* I tend to use camel notation
* I usually set any SQL Server database option to access compatible in order to facilitate any Access object names that might not be in accordance with my first bullet.
* Sometimes I assign a name to my modules, but that is rare.
0
 
LVL 10

Assisted Solution

by:VTKegan
VTKegan earned 0 total points
Comment Utility
<<Tell me how to quickly it is to find tblWorkRequests among 200 other tables in the GUI? Where if is WorkRequestsTbl you can tap the "W" to get to the general area>>

You would only have to type tblW, only 3 more key strokes.  In some time tests I just ran with a co-worker I can type tbl in under one second consistently. So it really isn't that much.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

Expert Comment

by:aikimark
Comment Utility
the object list must be sorted in name order for this to be rapid.  usually my lists are sorted in modified date order
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 0 total points
Comment Utility
If I understand correctly, the examples you gave, when things really broke because of spaces in the names, were because there was some code that was dynamically scrolling through all available objects in the database, and that code did not account for the possibility of space, i.e. it had something like

Pretty much -- I use brackets ([]) instead of quotes -- but it gets you to the same place. The SQL2K SP1 wizard didn't account for it though. SP2 fixed that.

You would only have to type tblW, only 3 more key strokes.

That is provided the GUI is set to let you get that many keystrokes. Typically I only get to "wo" the "r" would jump me to the "R" tables. Usually I'm working via RDP not directly on the server/ws.

Besides if you are using the SQL or Access GUI -- what is the header "Tables" or "Stored Procedures" or whatever.

Another issue is that in SQL if you prefix a stored proc with "sp" -- regardless of what database it will attempt to run the sp from the master database first.
0
 
LVL 11

Assisted Solution

by:Runrigger
Runrigger earned 0 total points
Comment Utility
in addition to VT's comment and in conjunction with JimD's earlier comment

tblWorkRquests is much easier to spot when quickly scanning code versus the lessor used convention WorkRequestsTbl

It may not seem much, but when you are supporting other's coding, one could vision a repidly frustrating situation.

Especially in jim's case when you potentially have two objects;
tblCustomers and/or qryCustomers
versus
CustomersTbl and/or CustomersQry

You try to do a Find/Search/Replace on that without it being less intuitive, more risky to error!

0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
For those who also work with SQL Server - I opened a similar thread at http:Q_26839323.html, named "Expert Topic: what locking do you use?"
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 0 total points
Comment Utility
My views, as I expressed in the original thread, are that spaces have no business in Object names. I use CamelCase along with a prefix Object identifier for Objects, and I use field identifiers for table fields. Makes life easier when I know at a glance that "txPartID" is a Text field, whereas "PartID" would require me to look into the table design to determine if it's Text or Numeric. I'm working on a few projects now that use a mixed bag of nameing conventions, and it can be difficult to switch back and forth.

In response to the thread that started this whole thing:

As the "Experts" in these threads, I feel that part of our role is to educate the newer developers as to better (or perhaps "more accepted") methods of doing things. I certainly appreciated that kind of input when I was in the newbie stage of my developer's life. To me, passing along information like this is no different than trying to teach the newbies why you should use a JOIN instead of a Domain Aggregate Function in a query, or why you should not add Lookup tables to tables in design view. It's part of our role, at least in my view.

Some will accept this sort of input, others will brush it off. In general, the ones who brush it off are the same ones who show up 2 months down the road with a query that "just won't work", and you find that it's due to whatever issue you tried educating them about in the first place :) .






0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
I guess everybody does that... but I was wondering, what is the fraction of times when it's really appreciated by the subject, and when it really empowers him.... I guess not too high.
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 0 total points
Comment Utility
I make a habit of naming the controls that were created by the form wizard, especially if any code refers to them.  I want to avoid control/field confusion.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 0 total points
Comment Utility
> I want to avoid control/field confusion.

I vaguely remember that this coincidence of the controls being named same as the fields, is for some deeply entrenched Access reason. Don't already recall the details, but the gut feeling is that Access likes them better to be the same.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Access doesn't care, it was a 'feature' (fault) of the form wizard.
0
 
LVL 15

Assisted Solution

by:ericpete
ericpete earned 0 total points
Comment Utility
First, I will stipulate that it's been several years since I did any significant development in Access -- more because of changed jobs than of a disdain for Access; I still use it every couple of weeks, but I'm not in the development realm.

Having said that, many years ago when I got hired in an "earn while you learn" job that included developing in Access, I took over several projects done by my boss, herself not a developer -- just someone who used the program. I quickly found that spaces were a nightmare, and it became pretty obvious, based on the specs for upgrades the users wanted, that her conventions in naming objects had to be re-evaluated.

That, among other things, brought me to EE, where Jim Dettman and several others always used tbl at the beginning of a table name, qry for queries, frm for forms. As I became more adept, that became habit ... but only because it WORKED. I've not been as firm over the years about the anathema toward the underscore, although the point about them is well-taken, but I don't use ANY other special characters.

ep
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 0 total points
Comment Utility
<<I vaguely remember that this coincidence of the controls being named same as the fields, is for some deeply entrenched Access reason. Don't already recall the details, but the gut feeling is that Access likes them better to be the same. >>

  Actually, you're better off with them being different.  Unfortunately, all the Access wizards since day one have generated controls with the same name as the field their bound to.  This can lead to problems, but it's not an issue as much as it once was.

  That change came about when the controls collection for forms was made the default collection.  Since then, Access has become less confused about what is being referred to.   Up until then, you never knew for sure what it was going after if you got sloppy and didn't use a full reference.

  But outside of that, I can't think of anything rooted in Access that liked field and control names being the same.

  I still rename all my controls with a prefix, but to be honest if I'm in a rush I won't bother to do that for a report as you can't refer to a field directly anyway, so it's a moot point.  That may be changed in A2007 and up with the new Report View option, which I've never really gotten into.  

  However when I don't take the time, I'm not sticking to convention and it is confusing when you move from a form to a report and see something different.  The other thing is that it is just a bad habit to get into.

  #1 rule in programming to remember is to always be as explicit as possible when coding.  I use a number of different languages and no matter which I've worked with, I find I get in a lot less trouble that way.

  And I can cause enough trouble on my own<g>.

JimD
0
 
LVL 61

Author Comment

by:mbizup
Comment Utility
vadimrapp1,

<but I was wondering, what is the fraction of times when it's really appreciated by the subject...>

I think that is largely dependent on how the message is delivered.


0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
@mbizup - no doubt you're right. So, do you see it often?
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 0 total points
Comment Utility
<but I was wondering, what is the fraction of times when it's really appreciated by the subject...>

I think that is largely dependent on how the message is delivered.


I will second that.

The way things work for me is that I usually only work the neglected questions from a lack of time to troll the zones on a regular basis. Add to that I don't only do SQL, Access, but also have experience in Server 2003, Terminal Server and Citrix, plus ancient experience in Oracle and other stuff. That is a nice way of saying I don't generally hit newbie Q's.

Back when I was hitting all the Q's and I would come across the spaces and such in object names and suggest the changes I listed.

That thought made me google it. I came across this Q:  reasons not to name it like this. Somehow I think this has been discussed before. ;-)
0
 
LVL 61

Author Comment

by:mbizup
Comment Utility
vadimrapp1,

>>  So, do you see it often?

Yes.  

We had a couple of regular Experts here who would consistently include a standard gentle 'aside' about naming conventions in their posts when they came across threads like the one in question.  They have been notably absent recently, but many other Experts do mention it as well.

I think the authors' situations need to be taken into account when posting this type of advice.  For example, if a question appears to be about a 'practice' database or a database in its infancy, it might be a good idea to suggest adopting good naming conventions sooner rather than later.    However, there are valid reasons that authors might not be able to adopt new naming conventions for the objects or project at hand, and that should be a consideration as well.  

I've never seen authors unhappy about Experts suggesting good naming conventions in a positive way.

The very few (in just over six years) frustrated responses I have seen have involved critical comments in cases where the Authors *knew* that the naming conventions were not the best, and for whatever reason were stuck with them.
0
 
LVL 61

Author Closing Comment

by:mbizup
Comment Utility
Thanks for participating!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now