Field Comments on Linked Access Tables

This is Part 1 in a series of articles discussing field comments on linked tables.

Let's discuss linking to tables in a Microsoft Access back-end mdb or accdb file.  I'm assuming that you already have your database split between a front-end and back-end file.  If those terms are unfamiliar to you, read Allen Browne's excellent primer on the topic first: Split your Access database into data and application.

Field descriptions carry forward from the back-end

When you link to Microsoft Access tables in a back-end database, the field descriptions from the source table show up in the front-end database automatically.

Field descriptions can be overwritten in the front-end

From the front-end database, you can overwrite the field descriptions in any linked table.  For example, if there are no descriptions in the back-end database, you can add them to the linked table in the front-end database.  The downside is that if you drop and re-link the table, then all those descriptions you added in the front-end are lost.

In the Linked Table Manager, there are [Refresh] and [Relink] buttons at the top-right part of the form.  Clicking [Refresh] on a table will not affect any column descriptions that you modified on the linked table.  However, if you click the [Relink] button, then all the column descriptions from the source database will get re-imported into the linked table.  If there are no column descriptions in the back-end database, then the descriptions in the front-end database will be wiped out.

Changing field descriptions on a back-end database is a pain...

It can be tempting to just set the field descriptions on the linked tables in the front-end database.  Changing field descriptions on the back-end database requires exclusive access to the table(s) whose field descriptions you want to change.  This means that no user can be accessing that table's data from their respective front-end database.

...but it's better than the alternative

Changing the field descriptions on the front-end database is easier because you are the only one using your copy of the front-end.  After all, that's one of the big advantages of splitting your database into a front-end and back-end.  

This is short-sighted thinking, though.  Inevitably, you will have to re-link the tables in your front-end.  And, if you only set column descriptions in the linked table(s), all of those descriptions will be lost.

In a perfect world, you will set the descriptions for all your fields in the back-end database file(s), before you deploy it/them.

Image by Daniel Reche from Pixabay