Expert Topic: Naming conventions in Access databases

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.

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.

<<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.

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!

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.

> 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?

* 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.

<<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.

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.

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!

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 :) .

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.

> 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.

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

<<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

<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. ;-)