... GPG On MS Access: Modifying Linked Tables

After two and a half decades of working with Access and answering questions in online forums, I am still surprised from time to time by questions about things I have long taken for granted. Here's a recent case in point.

The Question as Posed

First, the question, as posed, was a bit of a red herring (a lot of them are, as a matter of fact). The Original Poster (OP) wanted to know why his query seemed to "lose" new fields when he tried to add them. After a few back and forth responses with several other members trying to help, it finally turned out that the Access Relational Database Application in question was actually a split design, with a Back End for tables and a Front End for the interface elements including the query that was "losing fields". And from there it became clear what might be the underlying problem. The user was trying to add the new fields in the query and in the linked table in the front end.

The Solution Proposed

Once it became clear that the OP was trying to do this from within the Front End, the solution was much more straightforward. The new fields have to be added in the Back End and only then do they become available in the Front End.

I wish the solutions were all that simple although the detective work it took to get to it was a bit convoluted.

An Illustrated Example

As a follow-up, I decided to sketch out a quick illustration of the steps a successful table modification should follow in a typical Front End-Back End design. I used one of the basic sample data sets I keep for such purposes. It's based on Employees of an organization and the departments to which they are assigned.


Employees-Departments-Department Assignments

The relationship is many-to-many because each employee can be assigned to one or more departments and each department can have one or more employees. In this case, the relationship is intended to be sequential, rather than concurrent, in that employees would be assigned only to one department at a time. For that reason, the "fromDate" is included in the Primary Key for the EmployeeDepartment Junction table.

As a traditional Front End - Back End Access Relational Database Application, the forms are in one accdb and the tables in the other. The tables are linked, as can be seen in this image.

Linked Tables In Access FE, Identified by Arrow Icon

Employees and their assignments are managed through a traditional main form and subform design.


Main Form- Subform in Design View

Here's the same form in single form data entry view.


Main Form - Subform in Single View for Data Entry

After working with this Access Relational Database Application for a little while, the developer realized that it would be helpful to know when an employee leaves the organization. We want to add an Inactive Date to the Employee table.

However, not being experienced, the developer first tried to add the new field in the Front End, with this error.


Error Modifying Linked Table from Front End

A more experienced developer would immediately realize the problem and know how to solve it. Nonetheless, it does come up regularly, so I'm going to show you the next steps.

Open the Back End accdb with the tables. Open the Employee table in Design View.


Source Table in Back End accdb Open in Design View to Add Inactive Date

Now the appropriate field can be added and saved in the table.


InactiveDate Field Added in Source Table in Back End

Close the Back End accdb with the modified table in it. Reopen the Front End accdb. The new field should now be visible in the Linked Table. If it's not (and that's highly unlikely, of course), use the table relinker to refresh the link.