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.

top

top

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

- 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

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

- 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

- 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

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

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

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

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

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

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

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

- 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

- 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

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

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

reason: maintainability, error prevention

top

References are restricted to those really used in the application.

reason: error prevention, performance

top

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

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

reason: stability, user friendliness

ressources: MZ-Tools, vbWatchdog

top

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

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