Best Practices for Microsoft Access

(Last updated 2010-09-27)

These are, of course, just my opinion.  Feel free to disagree with me.

Verify Appropriate Jet Service Pack is installed

Splitting your app into a front end and back end

Decompile on a monthly basis if doing lots of work in VBA

Why I don't like cascade updates and deletes in Microsoft Access

Application and BackEnd version numbers

How do you know what version of your app a client/user is running?  I could've used an MDB property to contain this data but you can only view and update this programmatically. So I have a table in the FE and another in the BE which I update. I also display this information on the main menu and the About form.    If I have a change in the BE format, ie new tables, fields or indexes I use the BE version number to run some code which does the appropriate updates. Updating an Access Backend MDBs structure using VBA code

Displaying number of records in main tables.

In some apps I display the number of records in the main table, i.e. number of volunteers or number of vehicles. This really saved my butt once when I converted a file which had been emailed to me several days ago rather than the one they just emailed to me.

Date format in code

You shouldn't assume your users will always use mm/dd/yy format.  Especially if your app can be used outside your U.S. or Canadian organization. So see Date/Time: Return Dates in US #mm/dd/yyyy# format for queries.  Also note the time format in there as well.  I also like defining the regional date format on my system as dd/mm/yyyy just to help locate these problems.   If I was more disciplined this wouldn't be necessary.  <smile>

What Microsoft forgot in wizard generated code on forms

What Microsoft forgot to put in the code behind the wizard generated logic for most command buttons on bound forms was the following lines to save the record.

    If Me.Dirty Then _
        Me.Dirty = False

This is placed immediately after the On Error Goto ...  line.

(As a matter of syntax I prefer to have any single line If statements continued on the next line so it's easier to pick out such conditional statements.)

In the Command Button wizard it's the Form Operations and Report Operations generated code which is particular susceptible to this problem.

Examples of these kinds of command buttons are ones that call other forms or reports.   Any changes you've made to the data will likely not be displayed on the other forms or reports.  If you've just added a record you will likely get a syntax error in the Where clause on the form open but that's easy to locate and figure out.

What is particularly of concern though is that occasionally you have some field level validation such as a required field or a foreign key which hasn't been entered but defaults to 0.   Access will happily close the form and *NOT* save the record.  Without giving you a message.   The explicit save of the record will generate an error message for the user allowing the users to correct the data.

The below statement was what I always used in Access 2.0 and Access 97 but it appears to not work in Access 2000 and newer if the record doesn't require saving.
    DoCmd.RunCommand acCmdSaveRecord
I could put in error handling but why bother.

I always use Option Explicit and "Proper" case variable names in VBA code

I always want to be told whenever I have a variable I haven't declared.  Because it quite likely is a typo. 

I also proper case the variable name. For example "lngTransHeaderID".  Should I misspell the variable name, then once I hit the space bar, and the VBA editor does not change the case of the variable name from lower to proper case, then I also know I made a typo without waiting to get a compiler error.

Why is DoCmd.SetWarnings False bad?

For DAO I very much prefer to use Currentdb.Execute strSQL,dbfailonerror command instead of docmd.runsql.

The ADO equivalent would be
CurrentProject.Connection.Execute strCommand, lngRecordsAffected, adCmdText
where strCommand is the SQL statement, lngRecordsAffected is a Long variable in which ADO returns the number of records affected, and adCmdText identifies strCommand as a text command rather than, for example, the name of a table, view, or stored procedure.

If you're going to use docmd.setwarnings make very sure you put the DoCmd.SetWarnings True statement in any error handling code as well. Otherwise weird things may happen later on especially while you are working on the app. For example you will no longer get the "Do you wish to save your changes" message if you close an object. This may mean that unwanted changes, deletions or additions will be saved to your MDB.

One difference though is that docmd.runsql supports the Jet Expression Service which allows you to have expressions using form
controls and such.  However I always change these to values within the string that I pass to the Execute command anyhow.

Error handling

If you don't put error handling in your routines then Access can produce the message "a run-time error has been encountered" and it will exit your app.  Without telling you or your users what the error really was.  

At the top of your subroutine/function place the following line:

On error goto tagerror

At the bottom of your subroutine/function place the following lines:

    Exit Sub

tagError:
    MsgBox Err.Description
    Exit Sub
    Resume
End Sub

Why is Resume in the code when it will never be executed?

Adding records as required (on the fly) from within a combo box

This requires use of the NotInlist event as documented at http://www.mvps.org/access/forms/frm0015.htmI prefer my page at Using NotInList in a combo box Note that you want to use this option as appropriate in your application.  For example there is likely no need for a list of states/provinces or countries to be updated on-the-fly.  Also you may not want parts counter people adding customers without having the customers approved by the credit department.

When using Recordsets in VBA

Minimize the amount of code between the .AddNew/.Edit and the .Update lines.   This will minimize the chances of corruptions.   Also open the table using the appropriate read-only, update or append only options.   The performance hit may be negligible but in complex code it will help and life will be easier should decide to upsize to SQL Server.

Use a global database variable

When opening a recordset why use the following each time?

 Set MyDB = DBEngine.Workspaces(0).Databases(0)

Instead, in a form which is opened upon start and which always stays open, such as a Global Options form, open a global database variable and reuse it everywhere.

Compact on a regular basis

Compact both your front end, on your system as the developer, and the backend on a regular basis.   Not that I don't trust the compact process but I'd never compact without keeping a copy of the original MDB.  In other words don't compact from within the MDB.  See Backup, do you trust the users or system administrators? for more details.

Now how do you define regular?   I'd say weekly.   Quite possibly daily if there is hundreds of records being inserted on a daily basis.   I know one client who are adding a thousand records per day or more don't even compact weekly.

Backup, do you trust the users or system administrators?

Late Binding

Where feasible of course.   This will lessen any problems should the users upgrade their version of Outlook, Word, Excel, ... without telling you.

When in doubt add more tables

This is almost a joke but not quite.  If you're designing the tables and aren't sure if you need to add another table with a one to many relationship then you probably do.

One simple example is telephone numbers.   Suppose you allow for four telephone numbers?  What happens if the person has five?  Or six?  Do you fix the first phone number to be residence, the second to be business, third fax and forth cell phone?  What if the person has a pager?  What if someone wants to search by phone number because they see a phone number on their caller ID but no name? Do you then write a query which searches all four or five or six columns of phone numbers?   What if they have a satellite phone as well as a cell phone?

Far better to have a separate table containing the phone number on a one to many relationship to the person or business.  But how do you the decide what kind of telephone numbers are allowed?  Ie residence, business, fax, cell, pager or satellite phone?  Put these in another table.  

Then you reference the phone numbers of a person using a subform. Within that subform you reference the type of phone using a combo box.

So now instead of using a person table with four columns of phone numbers I've now added two tables, a subform for the persons phone numbers and a form for the type of phone.   More work you say?  Yup.  But this is a much more accurate representation of real life.

Avoid right joins in queries if at all possible

I'm embarrassed.  Thanks to fellow MVP John Viescas coauthor of SQL Queries for Mere Mortals for pointing out this error on my part.   The book is highly recommended by several fellow MVPs.

Users have been complaining of a slow process that they frequently use.  Worse they do many of these at one sitting.   Well it turns out that a bad habit of mine made this process take at least eight times as long.   I was using right joins in a query when I should've been using equal joins in this particular situation.  One of those tables has 200,000 records in it which makes this process that more inefficient.

Now frequently you can't avoid right joins as data may not exist in the other table.  An example in Northwinds might be an order without a shipper because the customer is just down the street and decided to pick up the order.

Links

17 Steps to Better VBA Code

[ Access Tips | AccessMain ]

Auto FE Updater   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Wrench and gear Granite Fleet Manager - the best designed fleet maintenance tracking and management system available

Comments email Tony  Search Contact Tony's Blog Privacy Policy Table of Contents

Website copyright 1995-2013 Tony Toews