Aggressive and Advanced sanitize levels remove necessary properties from some queries

[grv87]

#182 took a very daring approach.
ColumnWidth, ColumnOrder, DecimalPlaces, Format, ColumnHidden (=-1), AggregateType (<> -1) properties are necessary.
They cannot be deduced from SQL. And they are used in the real world.
Please, get them back for Aggressive and/or Advanced sanitize levels.

[hecon5]

@grv87, are you able to set the sanitize level lower and get what you need out of the columns?

Unless I'm mistaken, the ColumnWidth, ColumnOrder, DecimalPlaces, Format, ColumnHidden (=-1), AggregateType (<> -1) should be updated when you reconnect the passthrough query (at least, when I re-connect/ execute the query that's what happens).

At least in my testing, ColumnWidth, ColumnOrder are visual display properties and I'm unable to force Access to see them until I execute the query.

Can you give some context for what you're doing with those properties? Perhaps we could tweak the sanitize levels and add a table/query sanitize level like we had to for Forms and Reports.

[grv87]

@hecon5, I get what I need with Basic level. But I also get GUIDs and some other properties that (I agree) could be removed.

I can't tell in general, just in my case. I have about 10 complex SQL queries, and not all of them have these properties.

• ColumnOrder is set for queries where a user (me) reordered columns in Table view. This moving didn't change SQL. And if a user never moved columns, this property is not in the file
• ColumnWidth is set for queries where a user resized columns. Again, it's not present for views where I never resized any columns
• AggregateType is an aggregate function chosen by a user for a Total row. Again, in Table view, no SQL change
• Several SQL queries have calculations (divisions), and I wanted to show just one digit after a decimal sign. But I didn't want to round up values since this query was used somewhere else. So I set up Format and DecimalPlaces properties for these fields

I'm using Access 365.

[grv87]

Ah, I see.
We are talking about different kind of queries.

If a query goes to an external database, then Access could get some additional information from it, so it probably can be discarded.

But my queries don't use any external databases. They are just complex SQLs that can't be presented in Design view. I wrote them by hand.

[hecon5]

Are you directly showing users queries in split forms? It kinda sounds like you are (wanting to understand what the use case is).

If so, would a Datasheet type form be better if possible as it would be independent of the sub-query and you'd directly set formatting for the particular view. This avoids needing a specific column order or other formatting details, but would create another form, I recognize.

This would also allow you to hide PKs and / or other fields you don't want for some views, and keep the data there for sorting, as well as ensure you show users exactly what you want regardless of the query formatting or data type. it would also allow you to re-use a query for different context views and not maintain separate queries for essentially the same data set. We use these occasionally when the roll-up (Datasheet) we don't want all the details (eg: display short date vice the full timestamp for "date signed" type fields, but on the individual record we want the full stamp for review purposes). Or we have effectively a GUID as a PK, but that is basically unintelligible on a Datasheet (cue glazed over eyes) because it's not useful data. So we hide it on the datasheet roll-up, but it displays on the split form field (because it's actually useful one at a time in some instances).

[grv87]

I don't have (or need) any forms, just views.

[grv87]

According to docs, passthrough queries should have QueryDef.Type = dbQSQLPassThrough = 112.

So, the problem is here:
https://github.com/joyfullservice/msaccess-vcs-integration/blob/27a99c6d7aa5c626461f1afe4339419207e72c13/Version%20Control.accda.src/modules/modSanitize.bas#L208-L210

I checked my queries. Their Type property is still dbQSelect = 0.

UPD: But they have dbMemo "Connect" =""

[joyfullservice]

UPD: But they have dbMemo "Connect" =""

@grv87 - To clarify, are you saying that the handwritten SQL queries using only local tables have a Connect property with an empty string, as shown in the exported source file? If that's the case, then it sounds like these are being incorrectly flagged as pass-through queries as you pointed out in the above code lines.

If this is the case, we can add a second test to make sure that queries with an empty connection string are not flagged as pass-through queries for the purpose of sanitizing the content.

What happens if you change line 208 to the following?
If StartsWith(strLine, "dbMemo ""Connect"" =""") And Len(strLine) > 20 Then

Let me know if that fixes the issue for you...

[grv87]

To clarify, are you saying that the handwritten SQL queries using only local tables have a Connect property with an empty string, as shown in the exported source file?

Yes.

Let me know if that fixes the issue for you

It (mostly) fixes it. But I don't have pass-through queries, so can't say for everybody else.
IMO, it's better to use documented QueryDef.Type to avoid any new issues.

P.S. By mostly I mean that it keeps dbLong "AggregateType" ="-1" lines which could be removed. But these lines are kept for all queries, so it's a separate improvement.

[joyfullservice]

It (mostly) fixes it. But I don't have pass-through queries, so can't say for everybody else.

I have review the source files for pass-through queries, and this does work as expected.

IMO, it's better to use documented QueryDef.Type to avoid any new issues.

Yes, I agree, that would be a more robust way of determining the query type. However, the sanitization function is several layers removed from the actual database object, so it's not something we can readily access in that context. This could be refactored in the future, but for now checking the length of the property seems reasonable enough for our purposes.

P.S. By mostly I mean that it keeps dbLong "AggregateType" ="-1" lines which could be removed. But these lines are kept for all queries, so it's a separate improvement.

The main purpose for sanitizing these properties at all was that in pass-through queries they would (seemingly randomly) come and go, depending on whether that query had been run since the last export. This created a lot of "noise" in the export files and required careful review each time to make sure you were not missing actual changes. Sanitizing out the column information on (only) the pass-through queries resolved the issue for me and provided nice and clean exports that only reflected actual changes to the pass-through query code. As a general rule, I try to only tweak those things that cause actual problems for version control, and leave the rest of the exported file as intact as possible.

[hecon5]

Is this still a thing? IIRC there was some refactoring of the sanitize functions/class that may address this.