Microsoft Access Developer Best Practices

  • Understand your customer: Make sure you understand as much as you can about your customer. Visit the office / factory if possible, and see what they do on a daily basis. What is the core of their business? What do their customers need from them?
  • Written Requirements: Never assume you know what the customer wants. Explain to the customer (prefably in writing) what you believe he or she wants.
  • Study user interface design: There are many things that define a clear and natural user interface, including consistency (both within the application and consistency with other applications), standards, limited and consistent use of color, proper layout and alignment of fields and labels, proper grouping of information, tab order, clear and consistent error messages, and sufficient whitespace. You can download Microsoft's User Experience Interaction Guidleines (pdf)
  • Split the application (front end) from the back end db: This makes updates much easier, reduces the chances of corruption, and makes backups easier. The front-end should not contain any user data unless it is a temporary table required locally, but should contain all application related tables, such as choices for report names or options for sorting a form. Make sure your front end can find the back end in the current directory or on a network drive. That way the customer can copy the front and back ends to a test directory and test changes without affecting the live data, or copy everything to a laptop without network access.
  • Avoid macros — use VBA: You should avoid macros except for toolbars in Access 2003 and before, or web databases in Access 2010. VBA allows error trapping, and is much easier to maintain and reuse. It is also far more powerful than using macros. If you are building anything but the most trivial Access database applications (or 2010 web databases), you will need to know VBA, so learn it and use it.
  • Get formal training: You can learn quite a bit just playing around with Access, but if you want your applications to be bullet-proof and easily maintained, you need formal instruction in structured programming and object-oriented design. Take the time to learn how to do it the right way.
  • Queries: Use the * field to get all fields instead of listing each field separately when you want all the fields. When you add a field to the underlying table, it is automatically avaialable, and the query runs faster. This may seem obvious, but not everyone does it.
  • Build for Flexibility: Don't bury constants in code. Put them in a table (either system or back end db). For example, if you provide a user with a list of reports to print, your bound to add or remove reports later, so put the report names and any associated data in a table. Make sure your classes and functions use general purpose arguments so you can reuse them with few or preferably, no changes.
  • Build for reusabality (within and across applications): Avoid copying forms or reports just so you can add, remove, or change a few fields. Instead, use code to hide or show the few fields that are different. Then, when you need to make a change, you only have one place to update. Build your functions and classes to be general purpose so they can be reused again and again.
  • Create and maintain relationships: We're not talking the people kind here, but the relationships between tables. For example, if you have an order table with a unique order number and an order details table with products and quantities, when the order is deleted, you want the order details deleted too. Set up a relationship with the Cascading Delete option, and Microsoft Access does the delete for you automatically.
  • Build for maintainability: Write your application as if you expect someone else will be expanding it tomorrow. First, you cannot remember how and why you made every decision, so comment your code so you will remember, and make it clear enough so anyone will understand it. Use whitespace and indentations of loops or ifs in your code to make it readable. Write your code and application as if you were going to post all your objects and code online for review by experts. Make yourself proud of what you've done, it pays off in the long run.
  • Record all errors: Don't expect the customer to write down every error message or send you a screenshot. Trap all errors and write them to a separate database. Then, you can retrieve the exact message. Make sure you also record as many error details as you can, including the function and/or class where the error occurred. This is an example of a simple function using error logging: 
    '  This function shows startup options.
'
'  Written: 2010Oct20 MicrosoftAccessPros.com
'
Public Function hlpShowStartupOptions() as Boolean

    On Error GoTo hlpShowStartupOptionsError
    
    'Show each passed startup property
    Dim prpProperty As Property
    For Each prpProperty In CurrentDb.Properties
        MsgBox prpProperty.Name
    Next

    hlpShowStartupOptions = True

hlpShowStartupOptionsExit:
    Exit Function

hlpShowStartupOptionsError:
    hlpShowStartupOptions = False
    logWriteError Error$, vbCritical, "hlpShowStartupOptions"
    Resume hlpShowStartupOptionsExit
End Function