ColumnInfo Changes

[bclothier]

I'm trying to understand what exactly this change signify. This is for a combobox on a form:

...
  Begin ComboBox
    ...
-   ColumnInfo ="\"\";\"\";\"10\";\"10\""
+   ColumnInfo ="\"\";\"\";\"10\";\"510\""
    ...
  End ComboBox
...

There is no such thing as ColumnInfo in the designer, and I suspect it's an aggregate of various properties such as ColumnWidths and other related properties. However, I can't see what would have changed from 10 to 510 and what this means. There is nothing in the combobox's ColumnWidths so I was not expecting this to come up in diff. Any idea why it might be so?

[mwolfe02]

I'm not sure myself, but I did some investigating in my own repos. Here are some notes:

• As you likely know, the backslashes are escape characters for the embedded double quotes
  • So the unescaped ColumnInfo values would be:
    • "";"";"10";"10"
    • "";"";"10";"510"
• It appears that there are (n × 2) + 2 quoted entries where n is the combo box's Column Count
• I'm not sure what the first entry is for each column, but it looks like the second is formatting:
  • For example, here's the ColumnInfo for one of my 3-column combo boxes:
    • ColumnInfo ="\"\";\"\";\"\";\"\";\"\";\"$#,##0.00;($#,##0.00)\";\"10\";\"100\""
• The final two entries I'm guessing are some sort of enumeration, but I'm not sure what they represent.
  • Common values from one of my repos in rough order of most frequent:
    • Penultimate Entry:
      • 10 (every combo box except for two)
      • 4 (one instance; associated combo box was invisible...could be a coincidence)
      • 3 (one instance; associated combo box had a ValidationRule and ValidationText, which I don't usually use...could be a coincidence)
      • 12 (one instance; had a Tag value, but otherwise nothing noteworthy)
      • 8
    • Final Entry:
      • 100
      • 50
      • 0
      • 150
      • 510
      • 8
      • 20
      • 1
      • 2
      • 4
      • 24
      • 10
      • 200
      • 6
      • 60
      • 120

I'm not sure whether that helps at all. FWIW, I've never paid any attention to changes in the ColumnInfo setting. It's always just faded into the background noise that seems to come standard with Access version control.

[bclothier]

Thanks for the confirmation that it might be a background noise. It's one thing to ignore the Right and Bottom as noise, but ColumnInfo is much more mysterious which sets off the spidey sense... What did they change behind my back?!?

I just discovered that a unbound combobox does not have it while a bound combobox has it, which may be the key. It's based on the rowsource and the properties are probably derived from whatever's in the rowsource. This is a bit more baffling since the RowSource property is included in the source file but does not even appear in the diff.

[joyfullservice]

Being of the curious sort, I did a little testing to see what might change the value of the last item. If I set the row source directly to a table, I see the following:

ColumnInfo ="\"\";\"\";\"4\";\"4\""

If I set it to a query, (or embedded query), it changes to 510.

ColumnInfo ="\"\";\"\";\"10\";\"510\""

I haven't found a change that sets the last value to 10... I tried changing it manually in the source file, and importing the object, but it went back to 510 on the next export.

[bclothier]

Adam, I know you invested some effort into sanitizing the file. I'm assuming that ColumnInfo is one of them that you determined to be unsanitizable, right?

[joyfullservice]

Adam, I know you invested some effort into sanitizing the file. I'm assuming that ColumnInfo is one of them that you determined to be unsanitizable, right?

I haven't done a lot of testing on that particular value, mainly because it never caused any significant noise in projects I was working with. My general approach was to change as little as possible in the natively exported files, while at the same time working to reduce noise between builds and between development computers.

Are you seeing a lot of changes in the ColumnInfo values, or just a few here and there? Anything reproducible between systems?

[bclothier]

Understood. I did have a commit that wants to change 10 files with lines just like that and it's not immediately clear to me what would cause it to change as I wasn't expecting any changes in that. Unfortunately I already made a series of commit to document the changes that I was expecting so I may have lost visibility into what change and would have to do a deeper diff to see what might triggered it. I may have to dump it into a throwaway commit, and see if it recurs and maybe find the underlying cause.

[joyfullservice]

Gotcha. Ten files isn't just one or two, so I can definitely see the reason for the question. Feel free to post back if you notice anything further on this, otherwise we can just keep an eye out for more clues down the road.

[bclothier]

Ok, some more information.

For the pair of values, they represent the Caption and Format properties which can be passed down from the query or the table. It does not appear that an embedded SQL statement retains the Caption nor Format property; you can set them in the query design view but it won't be retained. Consequently, they must come from recordsource based on queries or tables.

To make things even more interesting, because those are derived from the underlying rowsource, we have cases where it won't be in sync. For example:

1. Create a form, add a combobox and base it on a query that has a caption or format set for one of the column included in the rowsource.
2. Save the form and close it.
3. Export the form as a text file.
4. Open the query in design view, change the caption. Save the changes.
5. Export the form as text file again. Note that the ColumnInfos remains unchanged.
6. Open the form in normal view and open the combobox. Close it.
7. Export the form as text file again. The ColumnInfos still doesn't change.
8. Open the form in design view and explicitly save, then close it.
9. Export the form as text file again. Still no changes.
10. Open the form in design view, reset the Rowsource property (I did this by cutting the name out, pressing enter, return to the property, then paste it back in, then press enter). Save the changes and close form.
11. Export the form as text file. This time, there's a change in ColumnInfos.

The penultimate value in the ColumnInfos correlates to the first visible column's data type as represented in the DAO.DataTypeEnum. This explains why 10 (aka dbText) is the most common value as we usually display text in the combobox. It does not correlate to the BoundColumn (if it did, 4/dbLong would have been the most common).

Based on this information, I can see why diff in ColumnInfos might seems apparently unrelated even if nothing has actually changed within the form. Those information are evidently all derivative of the underlying source in the rowsource. If we import the tables and queries before we import the forms, I have a hunch that ColumnInfos buys us nothing because Access may be able to re-constitute it during the import of the forms, so that may be a candidate for sanitization.

[bclothier]

BTW, I think I may have figured out the last entry. It's the field size in bytes. 255 characters => 510 bytes. Therefore, that diff in the original post was a change in the underlying field's length from 5 characters to 255 characters.

So in short, Access caches some metadata about the columns, with 2 additional information about the first visible column (which the user will interact with whenever they type in the editbox), and they all are ultimately derived from the rowsource.

[mwolfe02]

Very nice detective work, @bclothier!

[joyfullservice]

Very nice detective work, @bclothier!

My thoughts exactly!!

A very fascinating foray into the meaning of this mysterious value. Perhaps I will do a little testing to see if this value is recreated during build if it is sanitized in the source file...

[joyfullservice]

I did some testing with a combo box on a form, and this value was indeed regenerated when the form was imported. I tested this during a full build, importing just the affected form, and exporting/importing with the native commands and no sanitizing. In every case the ColumnInfo value was regenerated. Based on this testing, I think we can safely sanitize this value under the standard (aggressive) sanitize level, and avoid the VCS noise.

@mwolfe02 and @bclothier - any concerns with sanitizing this value?

[bclothier]

My hypothesis is that when I changed the tables, the changes wasn't apparent in the related forms until I built from source. Therefore, the commit with the changes to table wouldn't necessarily show diff in the ColumnInfos and it would not be until the file was built from source, it would suddenly change.

If that is the case, then I would say it is more beneficial to sanitize it. The only drawback is that it no longer provides a notice because a change in the captions, formats or the data type/field size can be fairly subtle. But I don't see how VCS would be able to detect this change especially when only the underlying tables/queries are edited, short of re-implementing the Name AutoCorrupt (please don't!).

If the distance between the edits and the diff is indeed great, I'm inclined to go for sanitizing it. Let us deal with the diff in the table/query that impacts the captions, formats and the visible column's data type/field size.

[mwolfe02]

I vote for sanitizing it.

From what I recall, the add-in includes an option to disable all sanitization anyway, right?

[joyfullservice]

From what I recall, the add-in includes an option to disable all sanitization anyway, right?

Yes, that is correct. Sanitizing can be changed or disabled in the options.

[hecon5]

@joyfullservice this appears to be addressed in the latest beta, and working for me.

[joyfullservice]

Yes, this value is being sanitized now. I will go ahead and close this out. 👍