Proper Access Naming

[Stacey Crowhurst]

Hi. I recently created a database from scratch and it quickly grew (19
tables, 48 queries, 22 forms and 6 reports). The naming conventions I
started out with are not the best and I need to change them but would like
some assistance. Is there anywhere that has "do's" and "dont's" for naming
in Access? Or should I just avoid non alpha characters and spaces? I want
to make names easy to reference and understand and also sequential for some
of my queries. Any help is greatly appreciated.

 

[Mr. B]

Stacey,

Here are a couple of links:
http://www.acc-technology.com/namconv.htm
http://www.dhdurso.org/articles/ms-access-naming.html

I would just add, I do not ever use any spaces in any naming of any of my
Access objects.

HTH
Mr. B
askdoctoraccess dot com

 

[Graham Mandeno]

Hi Stacey

I think the number of Access naming conventions is equal to the square root
of the number of Access developers!

However, I think ALL would agree that you should use only alphanumeric
characters, and that you should always start a field name with a letter, not
a number. After that, you can use whatever "convention" suits you.

FWIW, I invent a prefix of two to four characters for each table - for
example, this could be "prd" for Products or "emp" for Employees. Every
field in that table begins with that prefix: empID, empFirstName,
empLastName, etc. This makes life easier if you include two or more tables
in a query, because you can guarantee you won't have ambiguous names. It is
also self-documenting to some extent.

I also have a convention for primary and foreign keys. If a PK is an
autonumber, it is named <prefix>ID (for example, "prdID"). If a field is a
foreign key in a relationship, especially a many-to-many relationship, I
often append "FK". For example, an OrderItems table (prefix "oitm") might
include a field "oitmPrdFK", which is the prdID of the product being
ordered.

Finally, I NEVER use captions in table fields as these cause confusion by
masking the real field names.

Hope this helps!

 

[Michael Gramelspacher]

Hi. I recently created a database from scratch and it quickly grew (19
tables, 48 queries, 22 forms and 6 reports). The naming conventions I
started out with are not the best and I need to change them but would like
some assistance. Is there anywhere that has "do's" and "dont's" for naming
in Access? Or should I just avoid non alpha characters and spaces? I want
to make names easy to reference and understand and also sequential for some
of my queries. Any help is greatly appreciated.

Download any Microsoft Template database and look how they name tables, queries, etc.
There is nothing evil about having to bracket names. Script out anything from SQL Server and
everything is bracketed. There is an Access scripting tool named DBWScript, and it brackets
everything. Really though, as was said, do it the way you are comfortable with.

 

[Arvin Meyer MVP]

Hi. I recently created a database from scratch and it quickly grew (19
tables, 48 queries, 22 forms and 6 reports). The naming conventions I
started out with are not the best and I need to change them but would like
some assistance. Is there anywhere that has "do's" and "dont's" for
naming
in Access? Or should I just avoid non alpha characters and spaces? I
want
to make names easy to reference and understand and also sequential for
some
of my queries. Any help is greatly appreciated.

 

[Arvin Meyer MVP]

Hi. I recently created a database from scratch and it quickly grew (19
tables, 48 queries, 22 forms and 6 reports). The naming conventions I
started out with are not the best and I need to change them but would like
some assistance. Is there anywhere that has "do's" and "dont's" for
naming
in Access? Or should I just avoid non alpha characters and spaces? I
want
to make names easy to reference and understand and also sequential for
some
of my queries. Any help is greatly appreciated.

I avoid non alpha characters and use the L&R naming convention which is
based upon Hungarian notation. I have a utility for naming form and report
controls:

http://www.datastrat.com/Download/FixNames2K.zip

 

[Larry Linson]

... Is there anywhere that has "do's" and "dont's"
for naming in Access?

I'm really surprised that no one has recommended the most commonly used
naming convention, the Reddick Naming Convention. It is documented in
detail at Greg Reddick's site, http://www.xoc.net.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

Download any Microsoft Template database and look how they name
tables, queries, etc.

....and then run screaming from the room and do anything *but* what
MS does. Their templates are TERRIBLE in terms of suggesting
practices that are just not good at all for propert Access
development.

There is nothing evil about having to bracket names.

Yes, there really is -- it limits your ability to use derived tables
using native Jet SQL because the []. As Alias syntax breaks if you
need brackets within the SQL statement that is between the brackets.

Fortunately, from A2K3 on, you do get the option of using ANSI 92
syntax, which can do derived tables with the customary parentheses
instead, but that also means you have to change all your wildcards
(from * to & and from ? to _). I use a lot more wildcards than
derived tables, so it's not a very good tradeoff.

It took me about one year of professional Access development before
I completely abandoned names with spaces and special characters in
them. I've never looked back.

 

[David W. Fenton]

I'm really surprised that no one has recommended the most commonly
used naming convention, the Reddick Naming Convention. It is
documented in detail at Greg Reddick's site, http://www.xoc.net.

And as an antidote to L/R, there's our own Tony Toews's naming table
and field conventions:

http://www.granite.ab.ca/access/tablefieldnaming.htm

I don't follow his practices, but I can't really come up with any
particularly telling argument as to why his are wrong. I think the
main point is:

Have a consistent naming convention.

Use it.

Everywhere.

I've had various naming conventions over the years (mostly
incremental alterations of your basic L/R), and my code shows layers
in this regard. Am I scandalized when I go back to code using my
older conventions? Well, not so much. I'm annoyed, but on the "if it
ain't broke, don't fix it" principle, I just LEAVE IT ALONE. If I
ever find that a section of code needs to be refactored for some
other reason, I'll upgrade the naming conventions.

But otherwise, I leave it alone. I'm much more likely to introduce
an error than I am to make it better just by changing the naming
conventions, so if it's code that doesn't need to go through
significant re-architecting anyway, there's no real value in
updating it.

Clear as mud?

 

[Larry Linson]

I recommend Reddick, at least "Level One", because it is the most
widely-used convention. Thus, if you come in after someone, or they come in
after you, to work on a database, the probability is higher that the naming
conventions will be familiar.

That's why I don't subscribe to the old "Use some/any naming convention but
use it consistently" adage. I have worked on 'way too many DBs whose naming
conventions showed the developers were refugees from classic VB, or C++, or
C itself (or possibly from outer space) and were either useless or, worse,
misleading to the person coming along behnd to work on the same database.

The old advice is fine, I suppose, if you only ever work on databases you
created and maintained, and no one else has to pick them up; or if you are
in a development group which has adopted its own conventions (and don't pick
up DBs from others, or hire contractors, etc.).

Larry

 

[Michael Gramelspacher]

Download any Microsoft Template database and look how they name
tables, queries, etc.

...and then run screaming from the room and do anything *but* what
MS does. Their templates are TERRIBLE in terms of suggesting
practices that are just not good at all for propert Access
development.

There is nothing evil about having to bracket names.

Yes, there really is -- it limits your ability to use derived tables
using native Jet SQL because the []. As Alias syntax breaks if you
need brackets within the SQL statement that is between the brackets.

Fortunately, from A2K3 on, you do get the option of using ANSI 92
syntax, which can do derived tables with the customary parentheses
instead, but that also means you have to change all your wildcards
(from * to & and from ? to _). I use a lot more wildcards than
derived tables, so it's not a very good tradeoff.

It took me about one year of professional Access development before
I completely abandoned names with spaces and special characters in
them. I've never looked back.

Is this query using a derived table? This runs for me using Access 2003.
There is nothing checked under Options Tables/Queries
SQL Server Compatible Syntax (ANSI 92)

SELECT customer_id, DATEVALUE([start_date]) AS [Service Date], Rank,
SUM(DATEDIFF("n",[start_date],[end_date])) AS [Service Minutes]
FROM [SELECT s.customer_id,
s.start_date,
s.end_date,
COUNT(* ) AS Rank
FROM [Service Calls] AS s
INNER JOIN [Service Calls] AS s1
ON (s.customer_id = s1.customer_id)
AND (DATEVALUE(s.start_date) = DATEVALUE(s1.start_date))
WHERE (((DATEDIFF("n",s.end_date,s1.start_date)) < 120))
GROUP BY s.customer_id,s.start_date,s.end_date]. AS t
GROUP BY customer_id, DATEVALUE([start_date]), Rank;

In order to save this query, the derived table was enclosed in parentheses, but
when Access saved the query it changed the parentheses to brackets.

 

[David W. Fenton]

Is this query using a derived table?
Yes.

This runs for me using Access 2003.
There is nothing checked under Options Tables/Queries
SQL Server Compatible Syntax (ANSI 92)

SELECT customer_id, DATEVALUE([start_date]) AS [Service Date],
Rank, SUM(DATEDIFF("n",[start_date],[end_date])) AS [Service
Minutes] FROM [SELECT s.customer_id,
s.start_date,
s.end_date,
COUNT(* ) AS Rank
FROM [Service Calls] AS s
INNER JOIN [Service Calls] AS s1
ON (s.customer_id = s1.customer_id)
AND (DATEVALUE(s.start_date) =
DATEVALUE(s1.start_date))
WHERE (((DATEDIFF("n",s.end_date,s1.start_date)) <
120)) GROUP BY s.customer_id,s.start_date,s.end_date].
AS t
GROUP BY customer_id, DATEVALUE([start_date]), Rank;

In order to save this query, the derived table was enclosed in
parentheses, but when Access saved the query it changed the
parentheses to brackets.

Exactly what version of Access is this?

When I type () around a derived table (without ANSI 92 mode), Access
rejects it as invalid. If I use brackets with backets inside the
derived table, Access rejects it.

So, I'm curious what you're doing to make it work.

Nonetheless, I stand by my original point:

It's better to avoid names that require brackets in order for them
to be evaluated correctly.

 

[Graham Mandeno]

Hi Larry

I might have misconstrued, but I inferred from Stacey's original question
that s/he was talking about table and field names.

I'm not sure how Greg's VBA conventions are relevant in that context;

 

[Michael Gramelspacher]

Is this query using a derived table?
Yes.

This runs for me using Access 2003.
There is nothing checked under Options Tables/Queries
SQL Server Compatible Syntax (ANSI 92)

SELECT customer_id, DATEVALUE([start_date]) AS [Service Date],
Rank, SUM(DATEDIFF("n",[start_date],[end_date])) AS [Service
Minutes] FROM [SELECT s.customer_id,
s.start_date,
s.end_date,
COUNT(* ) AS Rank
FROM [Service Calls] AS s
INNER JOIN [Service Calls] AS s1
ON (s.customer_id = s1.customer_id)
AND (DATEVALUE(s.start_date) =
DATEVALUE(s1.start_date))
WHERE (((DATEDIFF("n",s.end_date,s1.start_date)) <
120)) GROUP BY s.customer_id,s.start_date,s.end_date].
AS t
GROUP BY customer_id, DATEVALUE([start_date]), Rank;

In order to save this query, the derived table was enclosed in
parentheses, but when Access saved the query it changed the
parentheses to brackets.

Exactly what version of Access is this?

When I type () around a derived table (without ANSI 92 mode), Access
rejects it as invalid. If I use brackets with backets inside the
derived table, Access rejects it.

So, I'm curious what you're doing to make it work.

Nonetheless, I stand by my original point:

It's better to avoid names that require brackets in order for them
to be evaluated correctly.

Microsoft Office Access 2003 (11.8166.8221) SP3

In SQL View I type

SELECT ...
FROM (.....) AS t;

Access saves query as

SELECT ...
FROM [....]. AS t;

It seems straight forward to me. No tricks.

 

[Rick Brandt]

Larry Linson's reasoning is excellent and I endorse it in all respects
given. I'll just add my $.02.

The thing to be aware of is your own motivation in using Access and in
using these newsgroups. My overarching goal is to provide as much value
to the client as I am able. While budget and time constraints may limit
how much value you can put into a project, we all have our own standards
and limits as to the thresholds we observe. I don't develop without
the use of comments nor without the use of a naming convention.

I think someone should point out that what most Access/VB people call a
"Naming Convention" is not necessarily what people in other environments
call a naming convention. What is very common in other programming
environments is a naming convention like...

ClassName
methodName
propertyName
variableName
CONSTANT_NAME

This is then combined with the standard of using no characters besides A-
Z, 0-9, and the underscore.

A naming convention that involves the use of prefixes to "type" an object
is not used much outside of Access/VB and outside of those circles it is
almost universally derided.

That prefixes "make it easier for the next guy" is unsupportable and
frankly laughable. For a developer to see a variable named something
like "emailAddress" and react with "What is it? Is it an Integer?. A
String? This is confusing!" would only mean that he is incompetent.

There are a great many people who *prefer* prefix-naming and are *more
comfortable* when they see it compared to when they don't. To them I say
go ahead and use it. Where I draw the line is when people suggest to
others that your code will look unprofessional if you don't toe the same
line. That is rubbish.

 

[Larry Daugherty]

Larry Linson's reasoning is excellent and I endorse it in all respects
given. I'll just add my $.02.

The thing to be aware of is your own motivation in using Access and in
using these newsgroups. My overarching goal is to provide as much
value to the client as I am able. While budget and time constraints
may limit how much value you can put into a project, we all have our
own standards and limits as to the thresholds we observe. I don't
develop without the use of comments nor without the use of a naming
convention.

The greatest value to the client is:

1. to deliver an application that does exactly what is required in
every respect.
2. to fully document and explain the application at every level so
that it will be readily comprehensible and maintainable by the next
developer. That ease of comprehension and maintenance has a real
value in terms of time and money.
(Also, bear in mind that the next developer could well be you
a few years down the road with many intervening projects). Years ago
I would often be shocked when I opened an old application to find that
it took a lot more analysis than it should have in order to understand
what I'd done reflexively years before. I underscore the point that
the naming convention is a large part of the documentation of the
application.

Adopting a naming convention requires an initial effort. Once
done, its use becomes reflexive and no extra effort at all. In other
words, you have to name objects something and having a convention in
hand makes your choices easy. Once you've adopted a convention then
coming back to old code is a relatively comfortable process. As Larry
Linson suggests, using the Reddick convention to at least Level 1 is a
very good and valuable beginning. That will cover most of your
objects most of the time.

The greatest single quality of the Reddick convention is that
it is so widely accepted by developers. It does all that it needs to
do and it is known throughout the world of Access and VB and VBA
developers. Perfect? No. Thoroughly comprehensive? No. New
objects will come into use. The world will keep on changing. It's
the best relevant convention available. It would seem reasonable to
me that ANSI or some other group might get the rights from Reddick and
make it a dynamic standard.

Some observations about people who proclaim that:

1. No comments nor naming convention is necessary because I know
what I'm doing. Usually they were actually unsure of what they were
doing and were afraid to let that show in their comments or code.
They're keeping all options open

2. My own convention is "better" than any other. I've been
developing software for decades. Larger corporations that recognized
software development as one of their core activities gave a lot of
thought to documentation standards and published documentation
requirement. An awful lot of the developers were so driven by their
own egos that they simply would not subordinate their "creativity and
freedom" to such constraints. The myth of the "eccentric genius"
pervaded the software world for years. These folks presented
themselves as "eccentric" ergo "genius". Some made their shtick fly.
Usually they were sent packing because they were rotten team players;
more concerned with their egos than with getting things done.

Because of my experiences in software development, I assume that the
applications I develop will endure and will eventually need to be
changed and enhanced to meet changing business needs. Everything that
I can do to enable those succeeding activities has a value. There are
caveats. I don't add extra effort that has no return in the current
iteration of the application.

If it is your anticipation that you are the only one in the universe
who will ever maintain your applications then adopting a personal
naming convention and ignoring the rest of the world might be a
reasonable thing to do. Be aware that just about any little
application that works will receive requests for enhancement.

It is my assumption in responding to posts herein that the person
asking the question is endeavoring to provide as much value as
possible.

In a nutshell:

If you intend to provide applications that may require extension over
time (and most of them do) by any developer whatever then you should
use the most widely accepted naming convention: Reddick.

If you will never let your source code be seen by anyone else then use
whatever convention adds the most value to you.

HTH
--
-Larry-
--

I recommend Reddick, at least "Level One", because it is the most
widely-used convention. Thus, if you come in after someone, or they come in
after you, to work on a database, the probability is higher that the naming
conventions will be familiar.

That's why I don't subscribe to the old "Use some/any naming convention but
use it consistently" adage. I have worked on 'way too many DBs whose naming
conventions showed the developers were refugees from classic VB, or C++, or
C itself (or possibly from outer space) and were either useless or, worse,
misleading to the person coming along behnd to work on the same database.

The old advice is fine, I suppose, if you only ever work on databases you
created and maintained, and no one else has to pick them up; or if you are
in a development group which has adopted its own conventions (and don't pick
up DBs from others, or hire contractors, etc.).

Larry

 

[elmer jackson]

who is Larry

Hi Larry

I might have misconstrued, but I inferred from Stacey's original question
that s/he was talking about table and field names.

I'm not sure how Greg's VBA conventions are relevant in that context;

 

[David W. Fenton]

Microsoft Office Access 2003 (11.8166.8221) SP3

In SQL View I type

SELECT ...
FROM (.....) AS t;

Access saves query as

SELECT ...
FROM [....]. AS t;

It seems straight forward to me. No tricks.

And it lets you include brackets inside those brackets?

I guess I don't have any SQL where I'd have encountered this change,
as I'd just type the square brackets to begin with, and would never
have any need for brackets for any of my field/table names.

 

[David W. Fenton]

A naming convention that involves the use of prefixes to "type" an
object is not used much outside of Access/VB and outside of those
circles it is almost universally derided.

I would say it is derided by people who have simply look down on VB
in general, not because of anything wrong with VB, but just because
they feel superior to it. Thus, their condemnation of reverse
Hungarian notation is not based on any actual argument, but just on
pure prejudice.

Sure, they can come up with justifications for their prejudices and
they *do*, at great length! But those objections are never as strong
as they claim them to be, and really come down to a matter of taste
and familiarity.

That prefixes "make it easier for the next guy" is unsupportable
and frankly laughable.

Well, just as it's laughable to claim that a GUI is more "intuitive"
than a command-line interface. Of course, put a person who knows
nothing about two particular programs, one with a GUI and one with a
command-line interface, and I can tell you right off that the user
will be getting something useful done in the GUI app before the
command-line app.

Why?

Not because the GUI is "intuitive," but because the GUI is
*familiar* and a known interface that is discoverable.

A standard naming convention works the same way (if not mis-used) --
it makes the ins and outs of the code more easily discoverable.

For a developer to see a variable named something
like "emailAddress" and react with "What is it? Is it an
Integer?. A String? This is confusing!" would only mean that he is
incompetent.

Is it a string or a variant? Both are valid variable types. And
handling certain things (if it's unitialized, the variant won't be a
ZLS, but a Null, for instance) then requires looking up the variable
declaration, instead of simply knowing what type it is without
having to look it up.

Is the information discoverable? Of course! But having the variable
type indicated in the variable name can make programming quicker, as
well as making inconsistencies more obvious.

There are a great many people who *prefer* prefix-naming and are
*more comfortable* when they see it compared to when they don't.
To them I say go ahead and use it. Where I draw the line is when
people suggest to others that your code will look unprofessional
if you don't toe the same line. That is rubbish.

Has anyone suggested that in the present discussion?

 

[huangda ½ú½­ÐË´ï»úе]

Larry Linson's reasoning is excellent and I endorse it in all respects
given. I'll just add my $.02.

The thing to be aware of is your own motivation in using Access and in
using these newsgroups. My overarching goal is to provide as much
value to the client as I am able. While budget and time constraints
may limit how much value you can put into a project, we all have our
own standards and limits as to the thresholds we observe. I don't
develop without the use of comments nor without the use of a naming
convention.

The greatest value to the client is:

1. to deliver an application that does exactly what is required in
every respect.
2. to fully document and explain the application at every level so
that it will be readily comprehensible and maintainable by the next
developer. That ease of comprehension and maintenance has a real
value in terms of time and money.
(Also, bear in mind that the next developer could well be you
a few years down the road with many intervening projects). Years ago
I would often be shocked when I opened an old application to find that
it took a lot more analysis than it should have in order to understand
what I'd done reflexively years before. I underscore the point that
the naming convention is a large part of the documentation of the
application.

Adopting a naming convention requires an initial effort. Once
done, its use becomes reflexive and no extra effort at all. In other
words, you have to name objects something and having a convention in
hand makes your choices easy. Once you've adopted a convention then
coming back to old code is a relatively comfortable process. As Larry
Linson suggests, using the Reddick convention to at least Level 1 is a
very good and valuable beginning. That will cover most of your
objects most of the time.

The greatest single quality of the Reddick convention is that
it is so widely accepted by developers. It does all that it needs to
do and it is known throughout the world of Access and VB and VBA
developers. Perfect? No. Thoroughly comprehensive? No. New
objects will come into use. The world will keep on changing. It's
the best relevant convention available. It would seem reasonable to
me that ANSI or some other group might get the rights from Reddick and
make it a dynamic standard.

Some observations about people who proclaim that:

1. No comments nor naming convention is necessary because I know
what I'm doing. Usually they were actually unsure of what they were
doing and were afraid to let that show in their comments or code.
They're keeping all options open

2. My own convention is "better" than any other. I've been
developing software for decades. Larger corporations that recognized
software development as one of their core activities gave a lot of
thought to documentation standards and published documentation
requirement. An awful lot of the developers were so driven by their
own egos that they simply would not subordinate their "creativity and
freedom" to such constraints. The myth of the "eccentric genius"
pervaded the software world for years. These folks presented
themselves as "eccentric" ergo "genius". Some made their shtick fly.
Usually they were sent packing because they were rotten team players;
more concerned with their egos than with getting things done.

Because of my experiences in software development, I assume that the
applications I develop will endure and will eventually need to be
changed and enhanced to meet changing business needs. Everything that
I can do to enable those succeeding activities has a value. There are
caveats. I don't add extra effort that has no return in the current
iteration of the application.

If it is your anticipation that you are the only one in the universe
who will ever maintain your applications then adopting a personal
naming convention and ignoring the rest of the world might be a
reasonable thing to do. Be aware that just about any little
application that works will receive requests for enhancement.

It is my assumption in responding to posts herein that the person
asking the question is endeavoring to provide as much value as
possible.

In a nutshell:

If you intend to provide applications that may require extension over
time (and most of them do) by any developer whatever then you should
use the most widely accepted naming convention: Reddick.

If you will never let your source code be seen by anyone else then use
whatever convention adds the most value to you.

HTH