Dan Waters
dwaters at usinternet.com
Tue Dec 28 13:21:24 CST 2004
Hmmmm . . .
How do you find databases like that where you can raise your rates?
Dan
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Tuesday, December 28, 2004 12:59 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Naming Convention (Query SQL correct but not
visibleindesign view)
I cannot count the number of times I've had to work on databases with no
naming convention. So I go to find the people table (no tbl of course) and
there are forms named people, queries named people, combos named people,
reports named people. My rates go up 50% if I have to deal with that.
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Tuesday, December 28, 2004 9:30 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Naming Convention (Query SQL correct but not visible
indesign view)
John,
You've really thought this through! I will no longer give FK's the same
name as the primary key.
I do one thing for a specific reason. When naming sub forms or reports they
go like this:
frmPeople or frmPeopleMain, and frmPeopleSub.
I do this so that all the People related forms are adjacent in the database
window.
Something else I do that is to almost never use an abbreviation in a field,
variable, control, or label name. This allows me or someone else to pretty
much be able to read the code to see what's happening without my having to
put in explanatory comments. It seems that I take more time writing and
modifying comments than I do writing code, so minimizing comments is good.
Also, when I can read code, I cannot misinterpret what is happening.
My names could look like:
tblPeopleMain
tblPeopleSub
lblDesignEngineer
txtDesignEngineer - field name = DesignEngineer chkDesignEngineer - field
name = DesignEngineerApproval dteDesignEngineer - field name =
DesignEngineerDate stgDesignEngineer varDesignEngineer
. . . . and so on.
You said you use an autoexec macro (I've never used macros in Access). I
use an invisible startup form, which calls a list of procedures and
functions in a standard module. Do you know of an advantage one way or the
other?
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Monday, December 27, 2004 8:07 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Query SQL correct but not visible in design view
Jim,
I actually have a very rigorous naming convention that I do follow,
religiously. My convention is:
I NEVER use special characters in any object name -
`~!@#$%^&*()_-+=|\}]{["':;?/>.<,|\ are all forbidden characters in an object
name (as well as a space).
I ALWAYS prefix objects - frm/sfrm, rpt/srpt, qry (or qxx), tbl/tlkp/tmm, -
and of course I don't use macros except for autoexec and autokeys, neither
of which can have a mac prefix.
I do NOT prefix fields with fld
I do NOT put the data type in the field name. I have just had too many
instances where the data type changes, and it is a PITA doing the search and
replace to make the change.
In field names, I use the first two characters of the table name, unless the
name is multiple words, in which case I use the first letter of each word.
tblPeople = PE
tblAutos = AU
tblPeopleAutos = PA
Etc.
I then use an underscore to delineate the end of the table name prefix
PE_
PA_
I then use ID for the PK
PE_ID
PA_ID
I use words, sometimes abbreviations for the words
PE_LastName or
PE_LName
Foreign keys are the PK name in the "parent" table, with the underscore
removed, and the 'ID' coming first
tblPeople
PE_ID <<<
PE_Lname
tblAutos
AU_ID <<<<
AU_Model
AU_Color
tblPeopleAutos
PA_ID
PA_IDPE <<<<
PA_IDAU <<<<
I place all FKs at the top of the table so that they can be easily found in
any table. I "swap" the ID and the table prefix so that it is obvious that
a field is a FK. If there is an ID after the underscore, then the field is
a FK (or the PK of the table). _IDPE, _IDAU etc.
This convention makes it dead simple to work with tables. There is NEVER a
field name in two different tables with the same field name, since the table
name prefix is always unique. I never get the "can't figure out what table
this field comes from" message from the query builder.
It is dead simple to look at a FK and "see" what the PK is called in the
"parent" table:
PA_IDPE <<
Take the IDPE, swap the ID and PE, and place an underscore between them
PE_ID
After using this convention for a little while, it "just works" and makes
the process of finding relationships between tables a breeze.
I have run into a few instances where there are hundreds of tables where I
have to maintain a table of prefixes to ensure that there aren't collisions
(two tables with the same prefix). In those instances it I usually have to
go with more than two characters to denote a table name word.
I have used this convention since about 1995 or thereabouts. I have shown
many developers how to use it and most of them love it.
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Monday, December 27, 2004 11:41 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Query SQL correct but not visible in design view
Do you use foreign key field names that are different from the primary key
name in the parent tables? What sort of naming convention do you use? I
always find it easier to understand a database when I can track fields such
as fldFirstName throughout a table structure. However, I must confess I
don't religiously follow this naming convention myself. Jim Hale
-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Saturday, December 25, 2004 12:40 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Query SQL correct but not visible in design view
That would explain it. I do not use the table qualifier unless the field
name is the same in different tables which is generally not the case in my
systems.
John W. Colby
www.ColbyConsulting.com
Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Saturday, December 25, 2004 12:23 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query SQL correct but not visible in design view
John,
If an SQL statement is put directly in SQL view of QBE, without having
first dragged any field into the QBE grid, relevant fields get displayed in
the grid only if their names in SELECT clause are qualified by the table
name.
For example -
(a) SELECT * FROM tbl1 - will not show any field in QBE grid.
(b) SELECT tbl1.* FROM tbl1 - will result in correct display in QBE
grid.
Note - This is as per tests conducted on Access XP running on Win XP.
A.D.Tejpal
--------------
----- Original Message -----
From: John W. Colby
To: 'Access Developers discussion and problem solving'
Sent: Friday, December 24, 2004 09:04
Subject: [AccessD] Query SQL correct but not visible in design view
I am building a SQL statement "on the fly" which essentially compares
matching fields in two different tables - Tbl1.Lname <> Tbl2.Lname. The SQL
runs but only the tables and their joins are visible in the QBE grid. There
are no fields visible etc. Does anyone understand what causes Access to do
this?
I have had the same thing occur even with something as simple as a SELECT
* from tbl1 inserted into the SQL view. Tbl1 is displayed, but nothing in
the grid. The query executes correctly.
Anyone?
John W. Colby
www.ColbyConsulting.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
***********************************************************************
The information transmitted is intended solely for the individual or entity
to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of or
taking action in reliance upon this information by persons or entities other
than the intended recipient is prohibited. If you have received this email
in error please contact the sender and delete the material from any
computer. As a recipient of this email, you are responsible for screening
its contents and the contents of any attachments for the presence of
viruses. No liability is accepted for any damages caused by any virus
transmitted by this email.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com