Microsoft Access. Adding a field to a linked table

Ever wanted to add a field to a linked Access database table from the frontend?  Perhaps you have copies of your software running in different environments and want to release a new frontend but not have to manually update every backend.  You can use VBA to create a field.  You need to import the backend table into the frontend, add the field, then export it to the backend database.  After that you need to relink the table.

To see the code click here. You can cut and paste the code into your own module, link a table and use the intermediate window to create a new field.

If you would like to add some comments or suggestions, leave a comment below.

Tags: , ,

One Response to “Microsoft Access. Adding a field to a linked table”

  1. Olaf says:

    Hi

    looks good, but unfortunately does not work.
    a) you reference subRunUpdateQuery which is missing from your code. Even though, I was able to find this sub using google to search your site I failed on
    b) if a table in the backend has constraints (references to another table) your code does not take care of this. The problem is, that the table is being copied from the backend to the frontend and modified – but can not be copied back, as your script tries to delete the backend table which is not possible because of the references.
    It would probably work if you check the contraints (references) on the backend for the specific table, take note of those in variables, copy the table to frontend, modify table in frontend. Then remove contraints (refences) in backend on that table, remove the table from backend. Copy table from frontend to backend, recreate the contraints (references) in backend.

    Olaf

Leave a Reply