Hi there,

This is my first post here. I just have finished writing the first version of a module that helps maintaining table fields descriptions and captions.



Problem statement:

When I need to quickly create many tables and fields, I try to give my fields self-explanatory names and avoid confusing abbreviations. This normally makes each programmer's life easier when he needs to create new forms or reports based on them assuming they're using a "Camel Case" naming convention.

But personally as a database engineer on larger systems like Oracle or even smaller ones like MySQL, I prefer sticking to the "Snake Case" convention in order to avoid compatibility and portability issues. And Access is not aware of that since it keeps on letting underscore symbols in my field captions when I create a new form or report.

It was obliging me to manually set each table field's caption, which is alot of work. So I began thinking about a way to automate this task with some VBA code and I wrote this module and published it on GitHub to enable other volunteers improving its code and functionality.

https://github.com/jporban/AccessTablesMetadata

The principle is easy. After having added some tables or field, I invoke the sub 'WriteFieldsMetadata' from the immediate window, with or without optional arguments (debug and overwrite options). With no arguments the debug mode is set on and the overwrite options are off by default.

It will loop through all my tables, excepted Access system tables which are filtered out and perform the following actions :
  • Description
    • If not set -> set it to field's name in proper case : "my_field" will become "My Field"
    • If already set -> do nothing

  • Caption
    • If not set -> set it to field's description (that we just set or that existed already)
    • If already set -> do nothing



The debug mode will output a detailed log to the console.

We can also force the update of fields descriptions and captions even if they are already set by setting the pOverWriteOptions optional argument:
- mOverwriteDescriptions (value = 1)
- mOverwriteCaptions (value = 2)

If I want to force both I will type in the immediate console
Code:
WriteFieldsMetadata mOverwriteCaptions + mOverwriteDescriptions
Or simply
Code:
WriteFieldsMetadata 3
According Microsoft documentation, it is also possible to define custom properties for fields (other than "Description" and "Caption") so I didn't hardcode the property names in the functions and subs GetFieldProperty(Value) and SetFieldPropertyValue.

I hope some of you will find this code useful. Don't hesitate to send me your comments and suggestions to improve it.

Cheers