Proper Access Naming

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.

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

"Stacey Crowhurst" wrote:

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.

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 prefixID (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!
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Stacey Crowhurst" wrote in
message ...
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.

On Thu, 12 Mar 2009 13:15:01 -0700, Stacey Crowhurst
wrote:

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.

"Stacey Crowhurst" wrote in
message ...
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.

"Stacey Crowhurst" wrote in
message ...
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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Stacey Crowhurst" wrote

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

Michael Gramelspacher wrote in
:

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"Larry Linson" wrote in
:

"Stacey Crowhurst"
wrote

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

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

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

"David W. Fenton" wrote in message
36.100...
"Larry Linson" wrote in
:

"Stacey Crowhurst"
wrote

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

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/