Access MVP (2010-2015)There is a discussion, *Database Standards when using outside Contractors*, in a group that I have been watching and it got me to thinking… While I have a set of standards (and a Model Database), I have not written them down. So every time I need to ask a Subcontractor to do some work for me I have to rattle them off and hope I remember them all which always results in, “Oh, yeah, I forgot that one…”. So, why not just type them up? Ahem, no answer for that except that I am doing it now!
You may think this is a waste of time and why bother… but consider the Developer coming in behind you. I get a lot of business from people that already have a database that needs some upgrading or tweaking AND, I spend more time trying to figure out what everything does and/or where it does it then implementing their request because no Standards were followed. And for you folks that think spending the time to fix the Captions on Fields and Labels is wasted, well, let’s look at the example below…
(c) Toon-a-Day.com
…enough said!
Now you’re asking if all my databases follow these Standards… the answer is *No*. However, the deviations were out of necessity not laziness. Standards are great and help everyone, especially if multiple Developers are working on the same project, but sometimes there are legitimate reasons to digress. When this happens make sure you tell everyone involved and document why and what you did. And finally, the Standards…
General…
- Use UNC Mapping
- Refresh Links/Relink Module with a *Persistent Connected* form
- Uncheck *Track name Autocorrect info*
- Set Subdatasheet to [None] but not before Step 3 or it will not stick
- Check *Use four-digit year formatting*. You can change this on the form for data entry should the Client want two digit years
- Uncheck *Show Smart Tags on Datasheets*
- Uncheck *Compact on Close*, this has been known to cause corruption
- When all done designing hide the Navigation Pane
- Document any passwords and any other pertinent information (and honestly, we can skip the one on the Code)15
- No ActiveX controls14
- All Databases must have Version included the Caption of the Main Menu or Switchboard
- All hidden fields will use #FF99CC as the Back Color
- Uncheck *Enable Layout View*
- Uncheck *Enable design changes for tables in Datasheet View*
Code (VB Editor)…
- All Modules must contain *Option Compare Database*, as well as, *Option Explicit*. (I have seen databases without either!)
- Comment code
- Error Handling and not *On Error Resume Next* across the board
- No Wizard use2
- Use UPPER CASE for all SQL keywords
- Indent code for easier reading
- Use continuation marks so I don’t have to scroll all the way to the right to see all your code
Tables…
- Must follow my Naming Conventions1
- Field Size convention and not the Access default3
- Every table must have a Primary Key
- The Primary Key should also be AutoNumber AND mean nothing to anything except Access
- Child and Junction tables should also contain a Primary Key, as well as, appropiate Foreign Keys
- No spaces, underscores and/or other wildcard characters in names16
- Non-use of the *Field Caption* property at the Table level
- Non-use of *Validation Rule/Text* at the Table level
- Non-use of Smart Tags
- No data entry performed via the tables11
- Fields…
-
- Must follow my Naming Convention1 and be consistent (Same name used across the Tables)5
- Field Names must mean something, i.e.: no abbreviated names6
- Non-use of Look-Up Wizard, Attachment and/or OLE Object4 and/or Hyperlink fields7
- Reserved Words for Access and/or SQL Server not used
- If you must allow Yes/No fields they should have a Default Value and not allow NULL’s8
- No spaces and/or wildcard characters16
- No image storage in the Database
- No unnecessary storing of calculated values9
- Set *Allow AutoCorrect* to No
- Access 2003 and lower remove the Default 0 (zero) that Access places on Number fields unless it will be needed
Forms…
- My default colors is to be used unless otherwise specified by the Client
- Main Menu or Switchboard – #FFFFFF
- Main Forms – #E6E4DD
- Forms used to filter or open Reports – #E3EBC7
- Maintenance Forms – #F2F1EE
- Allow Design Changes set to No (added 11.8.2015)
This… 
…is unacceptable, aside from being impossible to read. (Oh, yes there’s writing, it’s in white!) To see another example… Roger’s Access Blog: Really Bad Design Decisions
- Use of Standard Fonts12
- Size – no scrolling to see the entire form10
- Font size for data entry set to 9pt13
- Give Forms meaningful names
- Controls on forms should evenly spaced and lined up, i.e.: Close button, if you have one, should be placed in the upper right hand corner. Users are accustomed to the *x* at the upper right corner, moving the Close, to say, the lower right may cause confusion.
- Check the Tab Order
- Set Border Size to *Thin*
- Set Auto Resize too *Yes*
- Set Shorctut Menu to *No*
- Set Record Selectors to *No*
- Set Navigation Buttons to *No*
- Set Min Max Buttons to *None*
- Buttons…
-
- Uniform Images and/or Captions across all Forms
Reports…
- Same as Forms minus the Button and scrolling restriction
- Must contain a Revision Version and Date in the lower left hand corner
Queries…
- Use of UNION queries sparingly, if at all
- Give Queries meaningful names
- Use of MAKE TABLE queries sparingly, if at all
Relationships…
- Turn on Referential Integrity Enforced for all Relationships and…
- Apply *Cascading Updates* but not *Cascading Deletes*17
- And just so you are not stuck with just my point of view, have a look at Microsoft Access tips Default Forms and Reports by Allen Browne. You will also find how to make a Default Database for use within Access Templates at the bottom of the page.
2. The Wizard produces useless lines of code and I find it sloppy. I even use my standard for Navigation Buttons because the *error* message the Wizard produces when you reach the first or last record is useless.
3. Access has Defaults like TEXT, 50. (Access 2010 sets the Default to TEXT, 255.) I prefer the field size fits the data that is being entered. So, for State, where I only want to store the two character abbreviation I would make that field TEXT, 2.
4. While there is an equivalent for the OLE Object on SQL Server, I prefer not to use it. It takes up space and if I don’t upsize could cause the database to reach its 2 GB limit rather quickly.
5. If you used apAssociateID as the PK in tblAssociateProfiles then in tblActivities the field would be call aAssociateID
6. I once spent an hour trying to figure out what AYE Yes/No meant. It stood for Are You Employed?
7. None of those Data Types transfer to an SQL Server, so using them in Tables is not thinking ahead should the database be upsized.
8. Yes/No fields in an Access table can be empty and translate to contain NULL but not so on an SQL Server. Once upsized the field becomes a BIT which if NULL will not allow editing or adding to that Table.
9. The only justification for storing calculated values is because you need it for historical for say, invoicing.
10. Scrolling to see additional information in the Detail section is fine. Scrolling because the entire form does not fit on the screen is not. Read the applications Profile, which either I fill out or the Client fills out to find out the smallest monitor and make your form fit on that.
11. Tables are for storage not for Data Entry, not even DataSheet view. Create a form and you can make it look like a table.
12. Tahoma or Verdana. Whichever one is used it must be the same one throughout the database.
13. While the font size on the form can be 8pt, I prefer 9pt for data entry. It is easier to read.
14. Using ActiveX Controls has historically caused problems if the End User doesn’t have the same version. There is always another way to do it without an ActiveX Control
15. There are too many stories of how the Programmer left and took all the ADMIN passwords with them. If you don’t want anyone to see your coding then don’t use it.
16. This goes double for Underscores. If memory serves me correctly, this becomes an issue if you have cross paths with Oracle.
17. Data should never be deleted, period, instead hide it from view. Nor should it ever be pushed to a separate Archive table!
Edit: Green font items were added 9.12.2012 when I realized I had forgotten them!
Edit: Blue font items were added 11.9.2012 when I realized I had forgotten them!
3,232 total views, 1 views today
thanks for fixing my links, Gina!
Warm Regards,
Crystal
*
(: have an awesome day 🙂
*
You girls rock! Love your Database Standards.
Thanks!
Well thought out. Can’t say there is anything there I really disagree with.
Why the cascading updates if the PKs are hidden?
“The only justification for storing calculated values is because you need it for historical for say, invoicing.”
I think I would have said that about lookup values (which may change eg tax rate or delivery address), not so much calculated values.
The only reason I’ve ever stored calculated values was to make charting easier and faster.
Thank you!
*Cascading Updates*
Just means that whatever change is made to the Primary Key will be carried thru to the child table(s). While I have only once had to mess with the Primary Key I prefer to check it then wish I had checked it. One never knows, lightening could strick twice!
*Calcualted Values*
Two different things… Tax Rates are stored in their own field because they do change. Calcualted value would include *that* field so the Tax Rate for that Invoice stays the same. You may also choose to store the Price. However, if I am calculating the weight of something, ie: Length*Width*Density (calculation over simplified) then why store it when it will be available when the query is run? Really depends on the circumstance but generally, I don’t store unless Invoicng or Bill of Lading, etc…
Now, that I have had a chance to make a few tweaks…
Default Values
Added that!
Field Descriptions
Optional for me. I hardly ever bother so I would never pass this on to Subcontractor.
Yes/No fields
I still use them, only for Archive – Yes/No. Field is never seen, it’s my *trigger* to hide from viee
Archive tables
The problem with saying yes to that is the problem I am dealing with now. The *search for previous entry* was not searching the Archive table… 🙁 So when I merged them there were duplicates, 5,000 of them! So, I have banned them from being usedc in any of my databases. If size becomes an issue then it’s time to upsize to an SQL Server.
Tracking
I track by logging old/new data in a seperate log table (Log Field Changes)… similar to an Audit Trail that is stored in a seperate tblLog. I pull the Users from the Network ID. That way I get the old value/new value, record identifier and date/time and what form they were on when the change was made.
Wizards
Okay, I’ll go with you on that one. However, all the time it takes to change it wouldn’t it have been quicker to just do it to begin with?
Reserved Words
You must have missed that part, it’s there under Fields
Again, thanks for your comments and links! I think maybe you should start a blog! 🙂
WOW, Crystal big THANKS! When I get get a chance I will be incorporating some into my Article but some are already on my Access-Diva site under *Model Database*. However, you made me realize not everyone will wonder over there and take a look, so, I might want to put some of those items here!
Really appreciate you taking the time to read AND comment AND AND keep me on my toes! (No worries, as Luke says… he’s heard Developers go to **religious** wars over this topic…” 😮
P.S. I just checked because I too get confused, and on an SQL Server it’s a BIT which is 1 BYTE. There’s a joke in there somewhere!
P.P.S. Didn’t see a problem with your link.
hey Gina, I obviously screwed up the link to my analyzer … and while you are fixing that, please feel free to fix your article and remove that part about the Yes/No … think the data type is Bit anyway not Byte. I get confused too! Anyway I often use Integer to store Yes/No data so it CAN have a Null in the table 😉
~Crystal
Great list, Gina!
adding on … please feel free to incorporate whatever you want into your article 🙂
> “Access has Defaults like TEXT, 50”
in Access 2010, the default is 255. I put the default back to 50 (since imported data is going to come in big regardless) and specifically set sizes. If a Note is 50, I name it, for instance, NoteCust as opposed to NotesCust, which would be 255, and CmntCust which would be 100. When laying out forms and reports, it helps to have an idea how much data the control can hold.
> “Primary Key should also be AutoNumber”
All tables that users enter data into have an AutoNumber or Long Integer field that is a primary key or has a unique index. Sometimes I like to create values myself such as the TID in my table of tables: each table in the database has a TID (Table ID) that I keep track of (along with other things like unique long integer fieldname, 1-4 character table alias, link info, etc). This is handy for hooking in features I import from my tool databases.
~~~ field names
> “If you used apAssociateID as the PK in tblAssociateProfiles then in tblActivities the field would be call aAssociateID”
I do this differently. I name fields the same if they contain the same value, whenever I can. When creating queries, I will only pull all fields from ONE table using asterisk (*). From other tables, I will choose fields to display so key fields with the same name are not an issue because I simply won’t put the duplicates on the grid. Naming the same thing the same name gives enables more code to be reuseable too.
One of the reports that my Analyzer generates is a field list for the database sorted alphabetically. By keeping the field name the same, I can also easily see how many tables have a particular field.
My Analyzer
… the ultimate free Data Dictionary Tool by Crystal
In related tables, the Foreign Key field has the same name as the Primary Key field in the main table. Sometimes there might be more than one occurrence of the main PK. For instance, a table called Contacts with an AutoNumber Primary Key called CID would be in a table to keep track of relationships between people twice; these might be named CID1 and CID2 to qualify the CIDs. As long as the beginning of the field name is the same, they will sort next to each other alphabetically on my (Analyzer) Field List report.
When a field name contains “ID” in my databases, the data type is Long Integer and it is a key field.
> “No spaces, underscores and/or other wildcard characters in names”
I use underscores but never start a fieldname with anything but a letter. If the back-end ever moves to Oracle, guess I will be using DeepSearch (V-Tools) to change my fieldnames 🙂
V-Tools freeware by skrol29
> “tblAssociateProfiles”
there are different opinions on this — name tables starting with “tbl”, or don’t — … and we are all adamant that our methods are each best 😉 I preface other objects (Queries, Forms, Reports, Macros, Modules) but not necessarily Tables, unless the prefix adds meaning. For instance, when I import features from my contacts database, objects are prefaced with c_ like c_Contacts is a table, c_fContacts is a form, and c_rBirthdays is a report. Anything pulled in from my Analyzer is prefaced with a_. When I bring in objects such as this, I can ignore them for anything in the database I am working on because they are completely contained — I just hook into them — so when objects are sorted alphabetically, they are away from my other database objects.
~~~ DefaultValue for Numbers
For Access 2003 and below, remove the automatic default value on numbers to 0, especially for numeric foreign key fields unless 0 is a valid value and really is the default value. Look at tables already in your database to make sure default values are really intended. The Deep Analysis report (my Analyzer) shows default values for each field. You can quickly scan this report to see if any fields in your database have a 0 (zero) that you might not even know about!
~~~ Field Descriptions
Fill out Field Descriptions. This is what the status bar text will be set to when you drag that field onto a form. The StatusBarText shows up in the lower left corner of the screen on the Status Bar when you are in that field.
> “Yes/No fields in an Access table can contain Nulls”
not true. Yes/No is stored as Byte in table and can only be True or False. Allen Browne has an article about this here:
Why I stopped using Yes/No fields by Allen Browne
> ” The only justification for storing calculated values is because you need it for historical for say, invoicing.”
… and in data warehouse tables (which are generated on a schedule or on-the-fly) to make reporting easier 🙂
… and when using table triggers in 2010 and above. For instance, sometimes I define fields that store the Sans Accent version of text (ie: Cañabón, Canabon) for comparing. I use the table BeforeChange event to keep them updated. Makes much better sense to store them than to keep calculating them every time data is imported.
~~~ cross-reference tables
Cross-reference tables are named with both tables, or an abbreviation. For instances, the cross-reference table for Casez (legal cases) and Attorneys might be CaseAtts with AutoNumber Primary Key = CaseAttID. There would also be a unique index on the combination of CaseID and AttyID
> “9pt for data entry”
now that I am getting older and having more trouble myself, I usually use 10 pt.
> “Data should never be deleted”
There are exceptions to this
> “Nor should it ever be pushed to a separate Archive table!”
definitely more exceptions … my archive tables are in separate back-ends. Sometimes it is not practical to keep all the data — especially when only current stuff is being reported from.
> “No Wizard use”
I often use Wizards to make forms — then I change the heck out of them 🙂 Also use Wizards to make quick queries to find duplicates. I do not use Wizards to write code.
~~~ Tracking Fields
It is a good idea to add tracking fields to all tables (except tables that are used to provide values for lists and won’t be altered, such as a table of states in the country). Let them be the last 2 fields.
dtmAdd, date, DefaultValue = Now()
dtmEdit, date, DefaultValue = Now() –- update on the form BeforeUpdate event
in multi-user databases, add these additional fields:
IDadd, long
IDedit, long
These correspond to UserID. The values for these are supplied on the form BeforeUpdate event — could also be done as a table trigger on the BeforeChange event. I do not drag relationships to these fields since they are in every table and that would push or go beyond the limit of tables that can have RI to one table. Each front-end uses a database property to keep track of who the user is. This value is persistent even when the database is closed and then opened again. I have code on the Open event of the main form in the database (in case someone created the FE by importing) to create the database properties if they are not defined.
~~~ Reserved Words
Avoid using special words that Access might interpret to mean something different. A list of reserved words, and a free utility to scan your tables, can be found on Allen Browne’s website:
Problem names and reserved words in Access by Allen Browne
~~~ Indexes
Pay attention to table indexes. Remove the default that automatically creates an index on any field name containing ID;Key;Code;Num. When referencial integrity is enforced, Access creates an index behind the scenes for the relationship so it is not necessary to create another one that is visible. The Relationships report in my Analyzer shows all visible and hidden table indexes.
~~~ using code from others
when you get code from a site, put the URL where it came from, and who wrote it, in comment lines. Put example code with instructions and how to use it in more comment lines.
——————
Thanks for posting this topic. We all name things differently, of course, the main thing is to be consistent 🙂 Hopefully my comments come across with good spirit, as they are intended … just another perspective on a volatile topic 😉
Your site is so easy to read and looks great, Gina. Thanks for all the time you make to keep it updated.
Warm Regards,
Crystal
*
(: have an awesome day 🙂
*