We want to hear about your experiecne with Access templates

[Tony Toews [MVP]]

As we are discussing about Access 2007 features I would like to tell you
a reason why to use prefixes for Access objects: In contrast to the
database window the navigation pane allows you to open any object
without using the mouse. You just click F11 to active or show the
navigation pane, click Strg + f, then type a part of the object's name.
So if you are looking for a table, you just type "tbl" and all tables
will appear. Scroll through the tables until you find the requested one
and click on enter to open it. That won't work without any prefixes. ;-)

When I'm in Access 97 - A2003 when in the database container window I
can hit the first letter of the object name, for example P for
PurchaseOrders. And I'm there. No need to scroll. No need to use
the mouse.

Another reason to follow this standard is that it might be easier for
other developers to read and understand your databases and the objects
and code inside. But I'm sure you already know that. ;-)

Sure, but to me that's a fairly minuscule reason. And the advantages
of not using tbl, qry, frm and rpt far outweigh any reasons to use
them.

In this article you write:
"But still this isn't too bad. This would still be manageable. The
real pain point is the various query, form and report wizards. Where
you can only see 8 or 12 objects. The above database has 1200 queries,
450 forms and 350 reports. See the below screen."

If I had a database with so much objects, I would just drag the objects
(tables/queries) from the database window into the query instead of
using the wizards' dialogs.

And I do that a lot as well. But I still need to scroll around to
those objects. So hitting P for PurchaseOrders is very handy.

However that's about the only situation where you can do that. There
are many wizards where all you are presented with is a list of objects
and you have to scroll, scroll, scroll.

In "Tony's Table and Field Naming Conventions" you say:
"All field names are prefixed with the initials of the table name, e.g.
cName, ihInvoiceNbr and dQuantity"

That sounds somehow like redundant information. We don't give our first
names the initial of our last names, do we? Altough "mAndré" and "tTony"
looks funny somehow ... ;-)

When looking at a complex report you can tell at a glance what table
that field came from.

What when you have tables with the same initial?

You figure something out. Just like that page stated.

What if you have to change a table name after creating it or to add a
customer table from one database to another one that already contains a
table named categories? Seems to be a lot of work ... even if other
objects and code already refer to this object.

So I'd import the table and objects into a temporary database, do my
renaming likely be ensuring autocorrect is on and then import into the
final database.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[André Minhorst]

Hi Tony,

When I'm in Access 97 - A2003 when in the database container window I
can hit the first letter of the object name, for example P for
PurchaseOrders. And I'm there. No need to scroll. No need to use
the mouse.

You can't change the tab without using the mouse.

And I do that a lot as well. But I still need to scroll around to
those objects. So hitting P for PurchaseOrders is very handy.

However that's about the only situation where you can do that. There
are many wizards where all you are presented with is a list of objects
and you have to scroll, scroll, scroll.

I don't even remember the last wizard I've used. ;-)

When looking at a complex report you can tell at a glance what table
that field came from.

I normally know that by looking at the field name itself. And if there
is more than one field with the same name in the datasource the table is
shown in the control, too.

You figure something out. Just like that page stated.


So I'd import the table and objects into a temporary database, do my
renaming likely be ensuring autocorrect is on and then import into the
final database.

Autocorrect doesn't help you renaming objects in VBA code.

Tony, it might be useful not to use prefixes in object names in some
cases like finding objects fast in lists, but with the search field in
the navigation pane in Access 2007 you just have to type a part of the
object name to find it which is even more comfortable because you just
have to remember a part of the object name.

But using prefixes in field names still seems uncomfortable to me.
Thankfully this is no standard. ;-)

Bye
André

 

[Sascha Trowitzsch]

Hi Tony,

And my experience is that prefixing is an utter waste of time. Read
my web page on my reasons..

You have your reasons and others may have other reasons.
We could discuss that in hundreds of posts, one argument for the next. It's ok
if you use your own naming system. But here we are talking about MS templates
and Dany Hoter wanted a feedback. And to my opinion theese templates are neither
professional, as MS states, nor useful for professionals. They could indeed be
helpful for beginners in learning how to design databases. But from a "tutorial
database" I'd expect at least that MS follow their own guidelines. There are
several pages on MSDN ore other places where they advise use of prefixes.(One of
those:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp )
The question is: What's wrong with naming conventions other than "waste of
time"? (BTW: Does it really take time to write "tbl" odr "qry"??)
What other reasons NOT to use them? What's really bad? What's going wrong if I
use them?
I stick to it: For learners it is recommandable to use a standarized naming
convention as it helps them to think more structural in development.

Ciao, Sascha

 

[RoyVidar]

Hi Tony,


You can't change the tab without using the mouse.

Are you talking about the object categories in the database window?

Ctrl+Tab (Ctrl+Shift+Tab) in 97-2003

Another annoying (lack of) feature in 2007, while in the navigation
pain - what every other navigation utility I've seen is able to do,
is to allow the user to hit the same letter "umpteen" times to continue
down the row of objects starting with the same letter, in the 2007
version, it stops at the first.

tblBlah
tblFoo
tblThis
tblThat

you'll never get past tblBlah hitting the letter t ...

 

[RoyVidar]

I respectfully disagree with much of what you said.

I do agree with no spaces in table or field names. However spaces in
other object names are just fine.

I totally disagree with the idea of prefixing objects with the type of
object, such as tbl, qry, frm and report. There is no reason
whatsoever do this other than following "standards". And I see no
reason to follow "standards" in this case. Indeed the ease of use far
out weigh "standards"

It's just my experience, that prefixing really IS usefull.
Years ago it didn't prefix as well. I'm happy that I do now. And not at all for
following standards.
VBA code or SQL statements are much more transparent and faster to read, when I
use prefixes.
Queries like
"SELECT MAX([price]) As MAXPRICE FROM qryProductsFiltered"
"SELECT MAX([price]) As MAXPRICE FROM tblProducts"
are more understandable in design than
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a query)
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a table)

Do you think of the fact that most book authors indicate to use prefixes is just
due to follow standards?

Ciao, Sascha

Since Access does not allow a stored query to have the same name as a
table, your last sample isn't valid. There cannot be a stored query
and a table named Products in the same database.

Why are you being more descriptive when using your prefixes than when
you're demonstrating the "baaad" sample?

When using naming conventions that doesn't include prefixes, tags,
and whatnots, one is often more interested in being descriptive.

"SELECT MAX(price) As max_price FROM Products_Filtered_By_Something"
"SELECT MAX(price) As max_price FROM Products"

 

[André Minhorst]

Hi Roy,

Are you talking about the object categories in the database window?

Ctrl+Tab (Ctrl+Shift+Tab) in 97-2003

really cool ... I knew this must work some way. Thanks!

Another annoying (lack of) feature in 2007, while in the navigation
pain - what every other navigation utility I've seen is able to do,
is to allow the user to hit the same letter "umpteen" times to continue
down the row of objects starting with the same letter, in the 2007
version, it stops at the first.

tblBlah
tblFoo
tblThis
tblThat

you'll never get past tblBlah hitting the letter t ...

You're right. They might have forgotten this feature ... ;-)

What you can do is to type the first x letters of the object name
_fastly_. "tblT" for example will activate the tblThis entry.

Otherwise Strg + f makes finding and activating entries very easy.

André

 

[RoyVidar]

Hi Tony,



You have your reasons and others may have other reasons.
We could discuss that in hundreds of posts, one argument for the next. It's ok
if you use your own naming system. But here we are talking about MS templates
and Dany Hoter wanted a feedback. And to my opinion theese templates are neither
professional, as MS states, nor useful for professionals. They could indeed be
helpful for beginners in learning how to design databases. But from a "tutorial
database" I'd expect at least that MS follow their own guidelines. There are
several pages on MSDN ore other places where they advise use of prefixes.(One of
those:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp )
The question is: What's wrong with naming conventions other than "waste of
time"? (BTW: Does it really take time to write "tbl" odr "qry"??)
What other reasons NOT to use them? What's really bad? What's going wrong if I
use them?
I stick to it: For learners it is recommandable to use a standarized naming
convention as it helps them to think more structural in development.

Ciao, Sascha

You are saying that these templates "are neither professional, as MS
states, nor useful for professionals." because they fail to meet the
standards of an article that was last revised 13 years ago?

Are you sure Microsoft is still actively encouraging conventions based
on Charles Simonyis "Hungarian"? I mean, if you look at what they
recommend for .Net, one might find quite the opposite

"Do not use Hungarian notation.

Hungarian notation is the practice of including a prefix in identifiers
to encode some metadata about the parameter, such as the data type of
the identifier."

http://msdn2.microsoft.com/en-us/library/ms229045.aspx

A thought - maybe the templates (which I've never seen) reflects the
current thoughts of Microsoft on naming conventions?

No one has disagreed with you on the importance of using *a* naming
convention. There is nothing wrong with using *a* naming convention,
the disagreement, is about *which* naming convention to use.

 

[André Minhorst]

Hi Roy,

You are saying that these templates "are neither professional, as MS
states, nor useful for professionals." because they fail to meet the
standards of an article that was last revised 13 years ago?

Access is about 13 years old, VBA is too and hasn't changed a lot, so
why change the naming convention that a lot of access developers use?

Are you sure Microsoft is still actively encouraging conventions based
on Charles Simonyis "Hungarian"? I mean, if you look at what they
recommend for .Net, one might find quite the opposite

You are completely right. But on the referenced page Microsoft is
talking about VB.NET, not about Access and VBA.

Bye
André

 

[RoyVidar]

Access is about 13 years old, VBA is too and hasn't changed a lot, so
why change the naming convention that a lot of access developers use?

I'm not sure what you are asking.

If you are asking why Microsoft seem to have abandoned this naming
convention, I think you should ask Microsoft.

You are completely right. But on the referenced page Microsoft is
talking about VB.NET, not about Access and VBA.

Yes, I know the referenced page is about .Net, I even said so in my
reply, which you quoted ;-)

Then perhaps you can find some recent naming guidelines by Microsoft
on classic VB(A) encouraging this style? I've found that harder and
harder to find, without it having a warning at the top saying it is
archived, or this may not be the opinion of Microsoft currently, etc...

 

[Sascha Trowitzsch]

What even a beginner should not do but MS did in their templates:
- Use blanks in table names, query names and field names. ("Product ID" <>
"ProductID")
- Not to use prefixs for database objects (table "Products should be named
"tblProducts")
I respectfully disagree with much of what you said.

I do agree with no spaces in table or field names. However spaces in
other object names are just fine.

I totally disagree with the idea of prefixing objects with the type of
object, such as tbl, qry, frm and report. There is no reason
whatsoever do this other than following "standards". And I see no
reason to follow "standards" in this case. Indeed the ease of use far
out weigh "standards"

It's just my experience, that prefixing really IS usefull.
Years ago it didn't prefix as well. I'm happy that I do now. And not at all
for following standards.
VBA code or SQL statements are much more transparent and faster to read, when
I use prefixes.
Queries like
"SELECT MAX([price]) As MAXPRICE FROM qryProductsFiltered"
"SELECT MAX([price]) As MAXPRICE FROM tblProducts"
are more understandable in design than
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a query)
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a table)

Do you think of the fact that most book authors indicate to use prefixes is
just due to follow standards?

Ciao, Sascha

Since Access does not allow a stored query to have the same name as a
table, your last sample isn't valid. There cannot be a stored query
and a table named Products in the same database.

Why are you being more descriptive when using your prefixes than when
you're demonstrating the "baaad" sample?

When using naming conventions that doesn't include prefixes, tags,
and whatnots, one is often more interested in being descriptive.

"SELECT MAX(price) As max_price FROM Products_Filtered_By_Something"
"SELECT MAX(price) As max_price FROM Products"

I know that tables and queries are elements of the same database container and
accordingly can't be named the same. It didn't mean that those two samples
reside in the same database.
I just wanted to show, that in query design I can see at a glance what
underlying data sources are used. It makes a difference for me if a data source
is a table (updatable) or a query (might not be updatable).
In your example you distinguish between table and query just by descriptive
suffixes. To me that's not precise enough. Couldn't
"Products_Filtered_By_Something" be also a table resulting of an excel import?
I think the name of an object should describe its relation to real world
entities - a prefix decribes its _type_ .

Ciao, Sascha

 

[Sascha Trowitzsch]

Hi Roy,

A thought - maybe the templates (which I've never seen) reflects the
current thoughts of Microsoft on naming conventions?

No one has disagreed with you on the importance of using *a* naming
convention. There is nothing wrong with using *a* naming convention,
the disagreement, is about *which* naming convention to use.

Ok, you _should_ have a look at theese A2007 templates. Northwind database - now
also a template! - is the most important demonstration of access features in the
world. Hundreds of web site, books reference it. Some examples for the assumed
naming convention behind northwind:

"Inventory" - is it a table or a query? It's a query!
"Inventory Restocking" - a query? It's a table!
"Purchase Orders" > table; "Product Orders" > query.
"Sales Reports" - maybe a report? It's a table!
"Sales Analysis" - accordingly a table? No, it's a query.

And so on. There's no system at all behind theese names. Not good for learner's.
That's what I wanted to tell Dany.

Ciao, Sascha

 

[Tony Toews [MVP]]

The question is: What's wrong with naming conventions other than "waste of
time"? (BTW: Does it really take time to write "tbl" odr "qry"??)
What other reasons NOT to use them? What's really bad? What's going wrong if I
use them?

It's not that they're bad. Just that they're relatively useless.

From my other posting.

When I'm in Access 97 - A2003 when in the database container window I
can hit the first letter of the object name, for example P for
PurchaseOrders. And I'm there. No need to scroll. No need to use
the mouse.

There are many wizards where all you are presented with is a list of
objects and you have to scroll, scroll, scroll.

I stick to it: For learners it is recommandable to use a standarized naming
convention as it helps them to think more structural in development.

I can see no good reason for beginners to learn something like this
either. That said I've been doing database analysis, design and
development for just about thirty years so I'm no beginner.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

There are
several pages on MSDN ore other places where they advise use of prefixes.(One of
those:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp )

And that page is dated 1994.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

You can't change the tab without using the mouse.

Ah, yes, I see what you mean. You're correct there.

Autocorrect doesn't help you renaming objects in VBA code.

True enough. But it's very seldom that I have to import objects into
a new database. Other than my standard utility objects of course
which are prefixed with a z anyhow.

Tony, it might be useful not to use prefixes in object names in some
cases like finding objects fast in lists, but with the search field in
the navigation pane in Access 2007 you just have to type a part of the
object name to find it which is even more comfortable because you just
have to remember a part of the object name.

I haven't used A2007 a lot yet.

But using prefixes in field names still seems uncomfortable to me.
Thankfully this is no standard. ;-)

Actually more and more people are using my naming standard or have
independently come up with it. Still quite a minority though.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[André Minhorst]

Hi Tony,

Ah, yes, I see what you mean. You're correct there.

.... no, I'm not, as Roy posted a few hours earlier. But I'm happy that I
don't seem to be the only one who didn't find out how to change tabs in
the database window ... ;-) (btw: Alt-Tab/Alt-Shift-Tab does the job)

True enough. But it's very seldom that I have to import objects into
a new database. Other than my standard utility objects of course
which are prefixed with a z anyhow.


I haven't used A2007 a lot yet.


Actually more and more people are using my naming standard or have
independently come up with it. Still quite a minority though.

Well, I know another developer who uses his own naming convention and he
also seems to have some fellows who use his convention.

Good luck he didn't join this discussion also ... ;-)

André

 

[Tony Toews [MVP]]

Ok, you _should_ have a look at theese A2007 templates. Northwind database - now
also a template! - is the most important demonstration of access features in the
world. Hundreds of web site, books reference it. Some examples for the assumed
naming convention behind northwind:

"Inventory" - is it a table or a query? It's a query!
"Inventory Restocking" - a query? It's a table!
"Purchase Orders" > table; "Product Orders" > query.
"Sales Reports" - maybe a report? It's a table!
"Sales Analysis" - accordingly a table? No, it's a query.

And so on. There's no system at all behind theese names. Not good for learner's.
That's what I wanted to tell Dany.

Yowzer. That is ugly.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Well, I know another developer who uses his own naming convention and he
also seems to have some fellows who use his convention.

Good luck he didn't join this discussion also ... ;-)

What's his email address? <grin>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[André Minhorst]

What's his email address? <grin>

I'll give it to you just before I go on vacation (and offline) for a
couple of weeks ... ;-)

André

 

[Tony Toews [MVP]]

I'll give it to you just before I go on vacation (and offline) for a
couple of weeks ... ;-)

<big, belly laugh> Good one!

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/