Field Comments on Linked Tables

Mike Wolfe -

Editing field (a.k.a., column) descriptions (a.k.a., comments) on local Access tables is straightforward.  Open the table in Design View, then enter the description in the Description column to the right of the field name:

Of course, if you are following Microsoft Access best practices, then you will have split your database application between a front-end GUI and a back-end database.  And that means you have linked tables in your front-end.  

Managing field descriptions on linked tables presents a new set of challenges.  If you're not careful, you can document your entire database schema in your front-end application only to lose that documentation when you relink your tables.

And what if the back-end tables are in SQL Server?  That presents a different set of challenges.

Never fear!  In this six-part series of articles, I cover all those issues.  I also wrote several procedures that you can use to take the drudgery out of synchronizing field comments between SQL Server and your front-end linked tables.

Enjoy!

Part 1

This article discusses the considerations that are unique to dealing with linked tables where the source tables are in separate Microsoft Access files (as opposed to SQL Server or another RDBMS).

Field Comments on Linked Access Tables in MS Access
In part 1 of this series on field comments, we discuss maintaining column descriptions on tables linked to MS Access back-end files.
No Longer SetMike Wolfe

Part 2

This article describes the GUI and T-SQL techniques you can use to update field descriptions in SQL Server.  It also discusses the permissions required to do so.

Setting SQL Server Field Descriptions
There are two basic ways to set field descriptions in SQL Server: via SSMS and T-SQL. Let’s explore both.
No Longer SetMike Wolfe

Part 3

This article includes the UpdateColDescDDL() function that generates a T-SQL string to create or update column descriptions in SQL Server.

Use VBA to Generate T-SQL to UPSERT a Column Description
Let’s combine the stored procedures to Update and Insert SQL Server field comments into a single, all-encompassing solution.
No Longer SetMike Wolfe

Part 4

This article includes the PullColDescs() function that extracts column descriptions from a SQL Server source table and sets those descriptions to the local front-end TableDef links.

Set Field Descriptions on Linked SQL Server tables
What good are field descriptions in SQL Server if you don’t see them in your Access front-end? Here’s some code to bring those comments forward into Access.
No Longer SetMike Wolfe

Part 5

This article includes the ExtractFieldComments() function that returns a dictionary of field names and comments from a TableDef object.

Easy Access to Field Descriptions
The ExtractFieldComments() function returns a dictionary of field names and their descriptions from a TableDef object.
No Longer SetMike Wolfe

Part 6

This article includes the PushColDescs() function that creates or updates column descriptions on a source SQL Server table using the column descriptions from the front-end table link.

Pushing Field Comments to SQL Server
A VBA routine that takes field comments from a front-end linked table and pushes them to the corresponding back-end table in SQL Server.
No Longer SetMike Wolfe

Image by Kohji Asakawa from Pixabay