Catalog for the professional Access application
Version 1.01 (2014-05-24) by Karl Donaubauer                                                                                    versione italiana German version
 

What makes an Access application professional? Due to the diversity of requirements and styles there's no downright evidence but indications or best practices. Formally this is not another manual but a checklist of keywords with references that requires fundamental knowledge.

On the one hand it may serve as a reference for self-assessment of your own projects and for assessment by technically informed third parties (colleagues, customers, assessors). On the other hand it provides guidelines for the work of single developers and the cooperation in teams.

At the AEK15 (15th German Access Developers Conference, Oct 2012) I introduced a draft, discussed it with 200 colleagues and collected their feedback in a questionnaire. The list and links will be updated after further discussions, technology changes etc.

 
1. Database 2. Application 3. Programming
1.1 Normalization 2.1 Frontend and backend 3.1 VBA precedes macros
1.2 Primary keys 2.2 Naming 3.2 Naming
1.3 Indexing 2.3 Versioning 3.3 Declared variables
1.4 Relationships and RI 2.4 Ergonomics 3.4 References
1.5 Naming 2.5 Dedicated user interface 3.5 SQL precedes DAO/ADO
1.6 Optimized queries 2.6 Performance measures 3.6 Error handling
1.7 Optimized work with ODBC backend 2.7 Compact 3.7 Comments
  2.8 Dead objects 3.8 Formatting
 
1. Database
 
1.1 Normalization

Normal forms 0 to 3 are maintained at least.

0 = no calculations get saved
1 = atomic values per field
2 = non key fields depend on the complete key
3 = non key fields directly depend on the key

reason: data consistency
acceptable exception: performance optimization
resources: Wikipedia - Database Normalization

 

top

1.2 Primary keys

Every table has a primary key.

reason: identification at every opportunity, updatability

 

top

1.3 Indexing

Every table is adequately indexed.

- index on every field and combination of fields that is used for joins, filters, sorts
- indexes are as strict as possible ("No Duplicates")

reason: performance
acceptable exception: performance optimization, mass INSERT/UPDATE/DELETE

 

top

1.4 Relationships and RI

- relationships between tables are applied
- referential integrity is activated
- cascade update and delete are activated if possible/reasonable by the facts

reason: data consistency, overview, verification of table design

 

top

1.5 Naming

regarding tables, queries, fields, columns, aliases:

- no SQL/VBA/Access keywords in user defined names
- no spaces and special signs in user defined names
- consistent naming rules (naming convention or in-house system)

positive list i.e. acceptable/reasonable: A to Z, a to z, 0 to 9, underscore

reason: maintainability, error prevention
key words may cause problems despite the use of brackets

resources: Allen Browne's list of reserved words

 

top

1.6 Optimized queries

- indexes are used wherever possible
  - table scans are avoided
  - best candidate index is used
  - joins/filters/sorts through indexed fields
- only needed records (WHERE)
- only needed columns (SELECT)
- only required sorts
- no D-functions on linked tables

reason: performance

 

top

1.7 Optimized work with ODBC backend

- performance critical queries are located/executed on the server
  - pass-through queries
  - usage of server objects like stored procedures, views...

 - ODBC queries are not locally executed by JET/ACE
   - no Access specific functions
   - no references to Access objects (fields/controls on forms etc.)

reason: performance

 

top

 
2. Application
 
2.1 Frontend and backend

The application is separated in frontend and backend.

- tables are in the backend (or several backends), everything else is in the frontend
- every user gets his own local frontend (on Terminal Server its in a server directory)
- in multiuser settings there's an automatic procedure to distribute new frontends,
  i.e. they are not delivered manually to every workstation but by a tool, script, batch etc.

reason: avoid file corruption and data corruption, efficient maintainance of the frontends, reduced network traffic

 

top

2.2 Naming

regarding Access objects, controls:

- no Access/VBA/SQL keywords in user defined names
- no spaces or special signs in user defined names
- consistent naming rules
  - naming convention or in-house system
  - object/control types are recognizable through prefixes

positive list i.e. acceptable/reasonable: A to Z, a to z, 0 to 9, underscore

reason: maintainability, error prevention
key words may cause problems despite the use of brackets

resources: Allen Browne's list of reserved words

 

top

2.3 Versioning

The application has a versioning system that makes it possible for the developer and the user to distinguish different releases.

- minimum requirement: major and minor version numbers for the frontend
- depending on the type of application: version numbers for all involved files

reason: maintainability

resources: Wikipedia - Software versioning

 

top

2.4 Ergonomics

ISO 9241 on software ergonomics is fundamentally met:

- suitability for the task
- suitability for learning
- suitability for individualisation
- conformity with user expectations
- self descriptiveness
- controllability
- error tolerance

resources: Wikipedia - ISO 9241

 

top

2.5 Dedicated user interface

the user always works in a user interface specifically taylored for him:
  - sees only forms, reports, menus, ribbons etc.
  - does not see any table, query, code, design views

reason: user friendliness, stability

 

top

2.6 Performance measures

Important and well known performance measures are taken:
- name autocorrect is deactivated
- sub datasheets are deactivated
- permanent recordset with JET/ACE backend
- no D-functions on linked tables

resources: performance websites of FMS and Tony Toews

 

top

2.7 Compact

JET/ACE backends are compacted on a regular basis. Frontends are compacted or exchanged at least once in a while.

reason: object and data garbage gets eliminated, tables defragmented, query statistics updated

resources: Microsoft Jet Database Engine Programmer's Guide - Chapter 4

 

top

2.8 Dead objects

- obects that are no more used are deleted
- or for a short time are clearly identified by their name (e.g. zzz...) and will be deleted ASAP

reason: maintainability

 

top

 
3. Programming
 
3.1 VBA precedes macros

- VBA takes precedence
- macros are only used in special cases
  - autoexec
  - autokeys
  - data macros
  - web databases

reason: superior scope of services, flexibility, efficiency of VBA

 

top

3.2 Naming

regarding procedures, variables, constants:

- no VBA/Access/SQL keywords in user defined names
- no special signs in user defined names
- consistent naming rules
  - naming convention or in-house system
  - data types are recognizable through prefixes

positive list i.e. acceptable/reasonable: A to Z, a to z, 0 to 9, underscore

reason: maintainability, error prevention

Info: Allen Browne's list of reserved words, Reddick naming conventions

 

top

3.3 Declared variables

all variables are declared with their data type
- option "Require variable declaration"
- "Option Explicit" in every module

reason: maintainability, error prevention

 

top

3.4. References

References are restricted to those really used in the application.

reason: error prevention, performance

 

top

3.5 SQL precedes DAO/ADO

Queries/SQL is used for data operations except programming with DAO/ADO is really necessary, e.g. for loops with intermediate storage or calculations etc.

reason: performance of mass operations, locks by recordsets, error prevention – code is potentially error-prone

 

top

3.6 Error handling

sufficient error handling is done
- in the runtime version in every procedure

reason: stability, user friendliness

ressources: MZ-Tools, vbWatchdog

 

top

3.7 Comments

code is sufficiently commented
- the reason for non trivial procedures is explained in the beginning
- all non trivial pieces of code to an extent that allows informed developers to understand them

reason: maintainability

 

top

3.8 Formatting

code is formatted to be reader-friendly
- indentations i.e. horizontal structuring for loops, control structures etc.
- blank lines i.e. vertical structuring according to content criteria
- line breaks for lines with excess length

reason: maintainability

top