Best Practices

In our extensive experience with working with Microsoft Access and source code control, we compiled these best practices to work efficiently and to avoid common problems.

Split database into frontend and backend

You should split your Access application into two separate database files. One database file, the backend, should contain only the data tables. The other database file, the frontend, will contain all of the applications logic (e.g. queries, forms, reports, modules, etc.). The data tables are then linked to the frontend file. - This approach is covered extensively in many resources on Microsoft Access on the internet. You'll find further information on splitting your database here: https://support.microsoft.com/en-us/kb/304932

The frontend file should be added to and versioned in source code control.

Currently Ivercy cannot add individual table definitions to the source code control system. Therefore the backend would be added as one large binary file to source code control. This only makes sense if you have an empty backend without any data (except configuration data), so you can version the table definition and configuration data. But it is not recommend to put the data into source code control, as it will result in an unmaintainable binary file and does not provide much value.

Splitting an Application into frontend and backend is highly recommend generally with almost any Access application, regardless if you use source control or not.

Don’t embed (large) images in Forms and reports

Embedding large image files into forms or reports will create huge blocks of binary data in the source files of the forms and reports. This binary data does benefit only very little from source code control, as it is usually mostly static and changes to it are meaningless when viewed in their text representation. Still it slows down all the source code control operations considerably, as it has to be sent back and forth over network to your repository and has to be processed by Diff and other operations.

There are two approaches to achieve the same result without the drawbacks.

1.) If you have only a few distinct images that are used on many different forms (e.g. a company logo), we recommend, you put the image data into an Image-Column in a configuration table. Then write a public VBA function in a module that reads and returns the image data from the table. In each of the forms you call that function in the form's Form_Open-Event and set the PictureData of the Image-Control to the data returned by the function.

With Access 2010 Microsoft has built support for this approach directly into Access. Ken Getz created a How To article explaining the use. You can view it here: https://msdn.microsoft.com/en-us/library/office/gg490661.aspx

2.) If you got lots of different images, like one for each form, the approach described above has the disadvantage of massively bloating the database file containing the table with the image data. In this case it probably is more efficient to store the images as files in the file system and read the image data from those files.

But keep in mind that with the second approach,

  • you have to deploy new and modified images with new versions of your application.
  • the images are easily accessible by a file browser and your users could tamper with them.