Tip The Ten Commandments of Access (2 Viewers)

DCrake

Remembered
Local time
Today, 03:11
Joined
Jun 8, 2005
Messages
8,632
Thow normally not in the right section, I felt, that as all newbies visit this section first this is the best place to address the above.

The Ten Commandments of Access

And it came to pass that the cries and lamentations of the Access newbies were heard on high by the gods of the Database, and their hearts were moved to pity for their followers. And they opened their mouths and spake, saying: "Nevermore shall the young and innocent wander witless on their journeys!
We shall provide guidance to them, yea, and to all who wish to seek the paths of wisdom." And they caused these commandments to be written and placed before the eyes of those seeking enlightenment.
So heed the words of those who have come before you, and keep these commandments in thine heart as thou dost create thy Database application. If thou shalt only follow these commandments thy burden shall be made light and thy path shall be made straight.
  1. Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin.
  2. Thou shalt never allow thy users to see or edit tables directly, but only through forms and thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.
  3. Thou shalt choose a naming convention and abide by its wisdom and never allow spaces in thy names.
  4. Thou shalt write comments in your procedures and explain each variable.
  5. Thou shalt understand error handling and use it faithfully in all thy procedures.
  6. Thou shalt split thy databases.
  7. Thou shalt not use Autonumber if the field is meant to have meaning for thy users.
  8. Thou shalt not copy and paste other people's code without at least attempting to understand what it does.
  9. Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness.
  10. Thou shalt back-up thy database faithfully, working not on thy Production Database, but on the Prototype Copy, as it is right and good to do.
Thus spake the gods of the Database, and blessed be their names! And Blessed, too, are those who contribute to the Access Newsgroup - giving freely of themselves to serve those who hunger and thirst for knowledge and understanding!
 

HiTechCoach

Well-known member
Local time
Yesterday, 22:11
Joined
Mar 6, 2006
Messages
4,357
http://www.mvps.org/access/tencommandments.htm

and here are a few additions I have heard:

> Thou shalt never allow spaces or any other non-alphameric characters in thy names.


>Thou shalt write comments in your procedures and explain each variable. Only necessary if you write spaghetti code and use abbreviations in your variable names.

>Thou shalt understand error handling and use it faithfully in all thy procedures. If you understand error handling you know that it is not needed in every procedure.


> Thou shalt use the compiler directive, Option Explicit, and thou shalt explicitly define recordsets with the appropriate class object, DAO or ADODB.


> Thou shalt not use "I don't have permission to change it" as an excuse for not fixing bad table structure.

> thou shalt never store values from calculated fields
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:11
Joined
Sep 12, 2006
Messages
15,610
DC asked the Oracle "how much are these commandments?"

The Oracle said "They are free"

DC said "Then I will take ten"


Commandment 9
Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness.

but note that I have broken this commandment with regularity, and have not yet been struck down.


Some further possibilities


"Thou will take heed of the scope of variables. Thou will be especially careful not to unwittingly change variables passed as byref arguments, (for this is the default setting, which is an abomination to me) for this will verily produce strange side effects which will be hard to trace."

"Thou will become familiar with the usage of breakpoints and the F8 key"

"Thou will become familiar with the debug window"

"Thou will know and love the nz() function"
 

Rabbie

Super Moderator
Local time
Today, 03:11
Joined
Jul 10, 2007
Messages
5,906
DC asked the Oracle "how much are these commandments?"

The Oracle said "They are free"

DC said "Then I will take ten"

<<< Commandment 9
Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness. >>>


but note that I have broken this commandment with regularity, and have not yet been struck down.


Some further possibilities


"Thou will take heed of the scope of variables. Thou will be especially careful not to unwittingly change variables passed as byref arguments, (for this is the default setting, which is an abomination to me) for this will verily produce strange side effects which will be hard to trace."

"Thou will become familiar with the usage of breakpoints and the F8 key"

"Thou will become familiar with the debug window"

"Thou will know and love the nz() function"
Good suggestions especially especially the NZ() function.

In Commandment 9 i feel that over use of GoTo can lead to so-called Spaghetti coding that can be a nightmare to debug so I limit my use of it whereever possible
 

Lil' Rascal

y = x
Local time
Yesterday, 20:11
Joined
Feb 4, 2009
Messages
41
G-T-H said:
"Thou will know and love the nz() function"

Most excellent.

Can I throw a couple of thoughts out there?

"Take heed mine child and keep thine Immediate Window (ctrl+G) close at hand, for the BreakPoint draweth nigh."

"Be vigilant and never waiver in thine Watches as thou preparest for Deployment."
 

Atomic Shrimp

Humanoid lifeform
Local time
Today, 03:11
Joined
Jun 16, 2000
Messages
1,954
I shan't add any commandments, lest the Lords of Access smite me, but how about a beattitude?

Blessed are those who post again after their question is answered, for they will be assisted in future.
 

Atomic Shrimp

Humanoid lifeform
Local time
Today, 03:11
Joined
Jun 16, 2000
Messages
1,954
Woe betide any who plan their application to require constant design change to accomodate new data of the same kind, for they will never be released of the burden of development.
 

DCrake

Remembered
Local time
Today, 03:11
Joined
Jun 8, 2005
Messages
8,632
Dave (Gemma)

Your comment about Sendkeys

I have tried to use sendkeys in a VB project and Vista does not like it when you try and make a exe out of it as it treats this as a threat to security. Don't know how Access and Vista work together on this but it woould be nice to know.

David
 

neileg

AWF VIP
Local time
Today, 03:11
Joined
Dec 4, 2002
Messages
5,975
How about:
"Thou shalt understand datatypes and pick the most appropriate one for your data"
"Thou shalt applying formatting at the last possible opportunity for it is wonderous so to do"
"Thou shalt understand how dates are stored and the wonderous ways that dates are displayed in the many countries of the world. Know well that SQL abhors non-US date formats"
"Thou shalt not link thy databases to spreadsheets. The cloven hooves and wrongfull thoughts of spreadsheet users are an abomination unto the Gods of Data"
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:11
Joined
Sep 12, 2006
Messages
15,610
Dave (Gemma)

Your comment about Sendkeys

I have tried to use sendkeys in a VB project and Vista does not like it when you try and make a exe out of it as it treats this as a threat to security. Don't know how Access and Vista work together on this but it woould be nice to know.

David


Its Goto's I use really

all i ever do with sendkey is

sendkey "{esc}", now and again.

------------
Yes, congrats to Burnley - you can't have enough teams playing in Claret and Blue, can you?
 

Atomic Shrimp

Humanoid lifeform
Local time
Today, 03:11
Joined
Jun 16, 2000
Messages
1,954
Never shalt thou permit a user to enter a value as free text, if that value is to be subsequently tested as an attribute or control value.

And

Never shalt thou store more than one value in a single field, for in the later separate handling of those values, there will be much weeping and gnashing of teeth.
 

Rebecca G

New member
Local time
Yesterday, 22:11
Joined
May 27, 2009
Messages
3
I am primarily a self taught creater of Access databases and, create databases for others to use. I am particularly interested in commandment (10 Thou shalt back-up thy database faithfully, working not on thy Production Database, but on the Prototype Copy, as it is right and good to do. ). Is there a good process with which I can make changes to database objects and then roll the revised version out to the front-line folks without stopping production?
 

HiTechCoach

Well-known member
Local time
Yesterday, 22:11
Joined
Mar 6, 2006
Messages
4,357
I am primarily a self taught creater of Access databases and, create databases for others to use. I am particularly interested in commandment (10 Thou shalt back-up thy database faithfully, working not on thy Production Database, but on the Prototype Copy, as it is right and good to do. ). Is there a good process with which I can make changes to database objects and then roll the revised version out to the front-line folks without stopping production?

See:

Sharing an Access Database
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:11
Joined
Feb 28, 2001
Messages
26,946
I would only disagree on the near-absolute admonishment regarding GoTo.

While spaghetti code is surely to be abhorred, there are prececents even within Pascal, the brain-child of Niklaus Wirth, and it was Wirth who first stated that GoTo was obsolete. It isn't now and never will be obsolete as long as you have convoluted If/Then/Else nesting for which a short-cut to the end of a subroutine is appropriate. And as we all know, as much as we try to anticipate the convolution, sometimes there is no other way to do it.

You can end a segment with an End While, End Loop, End If and still write related code afterwards, but if you try to do an End Sub, it means something different syntactically.

You can, of course, do a Return in-line, buried in the middle of a complex nested, multi-layered IF block, and that is a short, syntactically correct way to do that, BUT... there is another rule or at least strong admonishment in general programming that says that all subroutines should have a single return-point (so you can see what is being returned by the routine.) The GoTo and Single Return rules are in conflict, and I treat the Single Return rule as the stronger of the two.

Basically, I would argue that real-world compilers aren't smart enough to do without a GoTo as a way to say "I'm done in the middle of this subroutine/function/code sequence so take the common exit/return/step-to-next-sequence point." It's the "done in the middle" aspect that, if you couldn't say that, would multiply the size of your code geometrically as the complexity grows linearly.

GoTo when used to jump from the front of the main code to the end is probably not such a good idea. GoTo when used simply to get out of a messy nesting is appropriate if re-casting the nesting doubles the size of the source code segment. And I've run into that particular bit of nastiness far too often to say it is rare.
 
Last edited:

Atomic Shrimp

Humanoid lifeform
Local time
Today, 03:11
Joined
Jun 16, 2000
Messages
1,954
Never shalt thou utter the words 'I don't need to solve [real-world scenario] because it will never happen". The man or woman that says this is to be taken to a public place and beaten with a trout.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:11
Joined
Feb 28, 2001
Messages
26,946
Mike G: Waste of a good trout. How about a channel mullet or a choupique (local Louisiana trash fish)?
 

statsman

Active member
Local time
Yesterday, 23:11
Joined
Aug 22, 2004
Messages
2,088
Thou shall never attempt User or Group Level Security on the original database. Thou shalt always use a copy. For when the sinner screws up the Security, he shall always have a safe harbour to which he may return.
Here endeth the lesson.
 

Lightwave

Ad astra
Local time
Today, 03:11
Joined
Sep 27, 2004
Messages
1,521
Thou shalt sit the user down preferably at management level and padlock him to a desk with paper copies of his own procedures and tell him to work through each procedure to ensure they are logical.

Thou shalt not let him leave until you are confident that they fully understand the critical paths and potential dangers.

During this minimum week period you shall remember to feed them
 

oumahexi

Free Range Witch
Local time
Today, 03:11
Joined
Aug 10, 2006
Messages
1,998
On adhereing to these wonderful commandments the Gods will surely favour you with new and wonderous employment opportunities, but beware, thou shalt be smitten if thou leavest in haste with little to no relevant and understandable documentation.
 

Users who are viewing this thread

Top Bottom