Best Practice for tables?

Almost the first thing I learned from one of my earliest Access
mentors was the "rename/copy" trick to ensure that I got the right
spelling of an object name to use elsewhere. It's become second nature
to the point where, even if I know the object name, I usually do the
rename/copy method almost out of habit.

My own naming conventions are quite traditional. I use three character
prefixes for tables, queries and reports and suffix ID for key fields.
However, I strongly believe the only crucial convention is
consistency.

George Hepworth, MS Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.

"David W. Fenton" wrote in message
. 1...
"Tony Toews [MVP]" wrote in
:

"Steve" wrote:

Repeatedly MVPs advise posters not to use spaces in object names
and here you are going against "learned" advise and putting spaces
in query names????

Correct.

I view myself as one of the "learned" advisors.

That means you don't often (or ever) use virtual tables. I couldn't
code one day in Access without writing SQL with at least one virtual
table, and queries with spaces in the names make that completely
impossible. You could get the same effect with the underscore
character.

Can you not just automatically put square brackets around all names?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Tony Toews [MVP]" wrote in
:

"David W. Fenton" wrote:

That means you don't often (or ever) use virtual tables. I
couldn't code one day in Access without writing SQL with at least
one virtual table, and queries with spaces in the names make that
completely impossible. You could get the same effect with the
underscore character.

What do you mean by "virtual table"?

SELECT tbl1.field1, vt.field2
FROM tlb1 INNER JOIN [SELECT tbl2.field2 FROM tbl2 WHERE ...]. AS vt

Because of the []. AS syntax, you can't have brackets inside there.

You can get around it for a while by using parentheses in some later
versions of Access (2000 and later) but if you save the query it
does get altered to brackets and you end up with the problem,
anyway.

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

"Douglas J. Steele" wrote in
:

"David W. Fenton" wrote in message
. 1...
"Tony Toews [MVP]" wrote in
:

"Steve" wrote:

Repeatedly MVPs advise posters not to use spaces in object names
and here you are going against "learned" advise and putting
spaces in query names????

Correct.

I view myself as one of the "learned" advisors.

That means you don't often (or ever) use virtual tables. I
couldn't code one day in Access without writing SQL with at least
one virtual table, and queries with spaces in the names make that
completely impossible. You could get the same effect with the
underscore character.

Can you not just automatically put square brackets around all
names?

Not in virtual tables. Brackets inside the virtual table definition
cause the whole thing to fail.

Geez! I didn't realize so many people don't use them!

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

"Tony Toews [MVP]" wrote in
:

"David W. Fenton" wrote:

Look at the object names. Queries have spaces in them and are
longer. Tables don't.

Huh. I didn't notice.

How do you process a list of tables and queries in code, then? Do
you use InStr()?

Why would I need to?

There are any number of circumstances where you might present a user
a list of queries and tables and need to do different things with
the choice, where you'd need to know the difference.

If you never do those, I guess it doesn't matter, but I just can't
get over the idea of putting spaces in the name of anything in
Access.

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

"Rick Brandt" wrote in
:

"David W. Fenton" wrote in message
. 1...
"Tony Toews [MVP]" wrote in
:

Now I have a much larger system with 160 tables, 1200 queries,
450 forms and 350 reports. But that would be rather long for
folks to look at the names.

But querydefs and tabledefs have a shared namespace, i.e., you
can't have an object of the same name in both collections, and
the Access UI in some contexts presents a combined list of both.
In a large project, how do you tell which is which in those
lists?

Speaking for myself I have never looked at a list of queries and
tables where I did not already know the name of the item I was
looking for. How else would I know which one to select? That
being the case, I already know whether it's a query or table.

I just fail to see where this is an issue.

You're depending on your memory and knowledge of the application to
supply for you the information that could be more plainly indicated.
I, for one, don't remember all the names of the objects in some of
my 10-year-old apps, nor recognize their functions. I'm very glad to
have an indication of what's a table and what's a query.

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

Grover Park George wrote in
oups.com:

Almost the first thing I learned from one of my earliest Access
mentors was the "rename/copy" trick to ensure that I got the right
spelling of an object name to use elsewhere. It's become second
nature to the point where, even if I know the object name, I
usually do the rename/copy method almost out of habit.

I do that, too, and always have. I don't see that it has any bearing
on the question of meta information in the names of table and query
objects.

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

"David W. Fenton" wrote in message
. 1...
Can you not just automatically put square brackets around all
names?

Not in virtual tables. Brackets inside the virtual table definition
cause the whole thing to fail.

Geez! I didn't realize so many people don't use them!

Now that I've seen your reply to Tony, I know what you're talking about (and
why you can't use square brackets).

You sure "virtual table" is a common way of referring to that? I've never
heard that term used for that scenario.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"David W. Fenton" wrote:

Look at the object names. Queries have spaces in them and are
longer. Tables don't.

Huh. I didn't notice.

How do you process a list of tables and queries in code, then? Do
you use InStr()?

Why would I need to?

There are any number of circumstances where you might present a user
a list of queries and tables and need to do different things with
the choice, where you'd need to know the difference.

I do have the situation where I have a report selection criteria form
with a bunch of combo, list boxes, date ranges and such. I start off
with a combo box of all the report names that start with the word
"user". Once the user selects the form I then enable the controls
appropriate for that report. So yes I do occasionally present such a
list but I want the list of report names to be user readable so spaces
work in that situation.

(BTW I have a table that maps control names on reports to control
names on that form. So when I update a report or add a new report I
run a bunch of code which analyzes the reports control source queries.
This then enables/disables the controls on the above mentioned form.)

If you never do those, I guess it doesn't matter, but I just can't
get over the idea of putting spaces in the name of anything in
Access.

To each thier own.

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/

"David W. Fenton" wrote:

What do you mean by "virtual table"?

SELECT tbl1.field1, vt.field2
FROM tlb1 INNER JOIN [SELECT tbl2.field2 FROM tbl2 WHERE ...]. AS vt

Because of the []. AS syntax, you can't have brackets inside there.

You can get around it for a while by using parentheses in some later
versions of Access (2000 and later) but if you save the query it
does get altered to brackets and you end up with the problem,
anyway.

I don't have spaces in table or field names. In query, form and
reports I do. So this isn't a problem for me.

I must admit I've never quite comfortable with the sub queries as you
are using above so I don't use them. I know I should but never have.

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/