Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
Expert Topic: Naming conventions in Access databases
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
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
Asked:
Who is Participating?
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.
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.
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.
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.
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!
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'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...
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...
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.
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.
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.
Rddick VBA conventions:
http://www.xoc.net/standards/rvbanc.asp
Access specific conventions midway down the page.
http://www.xoc.net/standards/rvbanc.asp
Access specific conventions midway down the page.
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.
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.
> 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?
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.
* 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.
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.
the object list must be sorted in name order for this to be rapid. usually my lists are sorted in modified date order
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.
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!
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!
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?"
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 :) .
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 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.
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.
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
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
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
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.
<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.
<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. ;-)
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. ;-)
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.
>> 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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month10 days, 19 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
I personally use identifiers without spaces, often very long, WithEveryWordCapitalized. But I don't feel any discomfort when I see totally different styles.