A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Best Practice for tables?



 
 
Thread Tools Display Modes
  #21  
Old August 21st, 2007, 11:04 PM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Best Practice for tables?

"Tony Toews [MVP]" wrote in
:

"Steve" wrote:

Unbelievable, Tony!!!!!!!


BTW Steve I don't like that attitude of yours. If you are going
to discuss things in the newsgroups please keep a civil keyboard
on your desk.


Uh, what Steve wrote looks a lot like what I would have said if he
hadn't gotten there first.

Except, I probably would have been more harsh.

I think, because of the fact that Access presents a combination of
the TableDefs and QueryDefs collections in some contexts, that it's
crucial to distinguish them by a prefix (it could be just t or q),
and because they share a single namespace (i.e., you can't have a
query and a table with the exact same name).

Likewise, I think it's great to be able to have a table and a query
with the same base name, so you can tell that the query presents the
data from a particular table. This is particularly important when
you have ULS and need to use RWOP queries. Your tblPerson would be
edited via the RWOP query qryPerson. That seems to me to be an
extremely logical way of organizing things.

How would you do the same thing without prefixes? Suffixes?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #22  
Old August 21st, 2007, 11:08 PM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Best Practice for tables?

"John Spencer" wrote in
:

Personal preference. On the other hand, I agree with Tony - I
don't see any reason to prefix tables with TBL or fields with fld.
That usually just means more typing for no real benefit that I
see. Again, if it works for you - go ahead and use it.


I don't do it for fieldnames in tables, but I do use it for tables.
Indeed, I use tbl for regular tables, tmp for temp tables and arc
for archive tables, often all three in a single application. If
you're not distinguishing different table types, though, I guess
there wouldn't be much benefit.

I don't distinguish query types (e.g., append vs. select vs. update
vs. delete) with prefixes, so logically, it would make more sense
for *me* to use my 3 prefixes on tables, and no prefix on queries.

But that would likely confuse other developers. Using the standard
tbl and qry makes it easier someone else to understand my design, as
it makes things very obvious, and keeps the table and query
namespaces distinct.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #23  
Old August 21st, 2007, 11:10 PM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Best Practice for tables?

"Arvin Meyer [MVP]" wrote in
:

I use the Leszynski/Reddick naming convention, almost
exactly as it was published. I only do it because other
programmers often work on the same projects and I like to maintain
some consistency with a generally accepted method. If you are the
only person working then whatever consistency you use is really up
to you.


Well, I think there's something to be said for using it even if
you're the only developer who is scheduled to work on the project.
It's a selling point to the client that you use the
industry-standard naming conventions, rather than some idiosyncratic
naming convention of your own, because someday in the future,
someone else may be working on the project.

I hate working on my very old projects, because I had no systematic
naming convention. It drives me nuts and eats up time while I figure
out what the hell everything is.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #24  
Old August 21st, 2007, 11:12 PM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Best Practice for tables?

"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?

And how do you associate similar objects, such as a secured base
table and its corresponding RWOP query?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #25  
Old August 21st, 2007, 11:16 PM posted to microsoft.public.access.gettingstarted
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Best Practice for tables?

"David W. Fenton" wrote:

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?


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

And how do you associate similar objects, such as a secured base
table and its corresponding RWOP query?


I don't use RWOP permissions or security. Although I'd figure
something out there.

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/
  #26  
Old August 21st, 2007, 11:16 PM posted to microsoft.public.access.gettingstarted
Arno R[_2_]
external usenet poster
 
Posts: 35
Default Best Practice for tables?


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

"Steve" wrote:

Unbelievable, Tony!!!!!!!


BTW Steve I don't like that attitude of yours. If you are going
to discuss things in the newsgroups please keep a civil keyboard
on your desk.


Uh, what Steve wrote looks a lot like what I would have said if he
hadn't gotten there first.


Unbelievable, David!!!!!!!

Except, I probably would have been more harsh.


I guess so yes... but why!!!!!!!

Arno R

  #27  
Old August 21st, 2007, 11:18 PM posted to microsoft.public.access.gettingstarted
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Best Practice for tables?

"David W. Fenton" wrote:

Uh, what Steve wrote looks a lot like what I would have said if he
hadn't gotten there first.

Except, I probably would have been more harsh.


Ok, maybe I'll let Steve get away with that one then.

I think, because of the fact that Access presents a combination of
the TableDefs and QueryDefs collections in some contexts, that it's
crucial to distinguish them by a prefix (it could be just t or q),
and because they share a single namespace (i.e., you can't have a
query and a table with the exact same name).

Likewise, I think it's great to be able to have a table and a query
with the same base name, so you can tell that the query presents the
data from a particular table. This is particularly important when
you have ULS and need to use RWOP queries. Your tblPerson would be
edited via the RWOP query qryPerson. That seems to me to be an
extremely logical way of organizing things.

How would you do the same thing without prefixes? Suffixes?


Yes, I would use some kind of suffix.

Granted to each thier own.

But to me on a large FE it is very, very handy to hit the first letter
of a group of objects in the database container window and get there
fast.

That overrides any of the q and t stuff. I still maintain that's a
complete waste of time.

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/
  #28  
Old August 21st, 2007, 11:38 PM posted to microsoft.public.access.gettingstarted
Steve[_10_]
external usenet poster
 
Posts: 608
Default Best Practice for tables?

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????
So besides wasting a lot of time typing long object names, you have to
remember to put square brackets around your query names. Do you put square
brackets around table names or do you use another set of rules for table
names?

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"Tony Toews [MVP]" wrote in message
...
"Gina Whipp" wrote:

Just curious... (Always willing to learn something new!)

What about in the case of DLookUp's (just one example), how would you know
where you are pulling the data from? Or do you table and query names
never
match?


I never use DLookups but code my own using recordsets and queries
usually defined in VBA code.

My table and query names can never match as my query names are quite
lengthy and descriptive and always have spaces. (Well almost always).
For example in a simple system I built for memberships the table names
are

Activity
Emails
GlobalOptions
Member
MemberFamily
MembershipActivity
MembershipEmails
Status

And the query names are

Acknowledgements to be sent via email
Acknowledgements to be sent via snail mail
Action - Acknowledgements
Action - Renewals
Active members
Activity - Renewal
Activity Summary - MembershipActivityDetails
AGM Notices to be sent
AGM Notices to be sent - Actiivity Append
Integrity checker - Renewals but not active
Membership Acknowledgements - Outstanding
Membership Activity History
Membership Renewals - Outstanding
Membership Renewals Outstanding
MembershipDetailReport
MembershipNoneDetailReport
Name Badges to be sent via snail mail
Non members with Email Addresses
Renewal Acknowledgements to be sent via email
Renewal Acknowledgements to be sent via snail mail for Pending R
Renewal Notices to be sent
Renewal Notices to be sent via email
Renewal Notices to be sent via snail mail

The code I used to produce the above is (as adapted from A97 help)

Sub ListQueryNames()

Dim qry As QueryDef, db As Database

Dim dbs As Database
Dim qdf As QueryDef

Set dbs = CurrentDb

For Each qdf In dbs.QueryDefs
Debug.Print " " & qdf.Name
Next qdf

End Sub

Sub ListTableNames()

Dim qry As TableDef, db As Database

Dim dbs As Database
Dim tdf As TableDef

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
Debug.Print " " & tdf.Name
Next tdf

End Sub


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

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/



  #29  
Old August 21st, 2007, 11:52 PM posted to microsoft.public.access.gettingstarted
Steve[_10_]
external usenet poster
 
Posts: 608
Default Best Practice for tables?

No one has yet mentioned form and report names. I prefix all form names with
Frm and prefix all subreport names with SFrm. I get two distinct advantages
from this. First, when I look at the forms database window, all form names
appear first and all subform names are grouped separately from form names.
Second, it helps to catch errors when referencing forms and subforms..

The way I name reports and subreports is similar to the way I name forms and
subforms. I prefix reports with Rpt and I prefix subreports with SRpt. The
advantages for reports are the same as the advantages for forms.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"Kamitsukenu" wrote in message
...
Hi there,

The company I work for mail items dependent on destination, weight and
speed
of delivery.

I'm trying to make a system in Access, and during setting up all the
tables,
I'm getting myself more and more confused.

I have the following tables;

(tblCountry) - holds the Country names
(tblDispatchMethod) - the service the item is sent by
(tblSupplier) - who the item is mailed by

Now, as dispatch of items are dependent on the weight, I have another
table
which combines all the above information.

TblRoutingSystem contains
'CountryName' which refers to 'tblCountry'
'DispatchMethod' which refers to 'tblDispatchMethod'
'NameofSupplier' which refers to 'tblSupplier'
'WeightBandStart' and 'WeightBandEnd' which refer to (surprise, surprise)
the start and end of the weight bands for deciding the correct supplier
for
mailing.

Does this sound about right, or should I be split them out further or
should
I be merging more stuff together.

in fact, can someone go through the pros and cons of creating more tables
to
suit the data?

Thanks

K




  #30  
Old August 22nd, 2007, 12:02 AM posted to microsoft.public.access.gettingstarted
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Best Practice for tables?

"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. And I hold a
contrary opinion to most on this topic. As I do others. So my
attitude varies somewhere between tough and who cares.

So besides wasting a lot of time typing long object names,


Reread a previous posting on this topic. I do *not* type long object
names.

you have to
remember to put square brackets around your query names.


Whenever I use queries embedded in VBA I create the query the first
time using the query builder which takes care of everything anyhow.
So that's a non issue too.

Do you put square
brackets around table names or do you use another set of rules for table
names?


No, no need.

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/
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:24 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.