Query is imported different as it should in "Build from source"

[Tanarri]

Hi,

I work on 2 different PC with the same addbc (access 2013) and export them after a change. Then the exported fils will copied with githb to the other PC an there I will do a "build from source". This is the same workflow as described https://github.com/joyfullservice/msaccess-vcs-integration/issues/216#issuecomment-831915803

At the beginning of the workflow I builded on bothe PCs the accdb from source with option "Save Query SQL" and "Force import of original SQL". This is the base for my testing. Then I exported -> copy to github -> build from source on other PC -> exported .... There should be no changes found from "github desktop". But I had several differences in queries, but not all queries. (modules, classes had no differenes).

I picked one query and made debug outputs in "clsDbQuery.Import" to get the part, were the query is changed.

Debugcode

        ' Update query def with saved SQL
        If strSql <> vbNullString Then
            Set dbs = CurrentDb
            If strQueryName = "qry_export_kataloge_frachtkosten_preise" Then
                Stop
                Debug.Print ".sql file:" & vbCrLf & strSql
                Debug.Print "==== Before import of .sql"
                Debug.Print "Imported from LoadComponentFromText:" & vbCrLf & dbs.QueryDefs(strQueryName).SQL
                dbs.QueryDefs.Refresh
                Debug.Print "==== Before import of .sql (after querydefs.refresh)"
                Debug.Print "Imported from LoadComponentFromText:" & vbCrLf & dbs.QueryDefs(strQueryName).SQL
            End If
            dbs.QueryDefs(strQueryName).SQL = strSql
            If strQueryName = "qry_export_kataloge_frachtkosten_preise" Then
                Stop
                Debug.Print "==== After import of .sql"
                Debug.Print "Imported from LoadComponentFromText:" & vbCrLf & dbs.QueryDefs(strQueryName).SQL
                dbs.QueryDefs.Refresh
                Debug.Print "==== After import of .sql (after querydefs.refresh)"
                Debug.Print "Imported from LoadComponentFromText:" & vbCrLf & dbs.QueryDefs(strQueryName).SQL
            End If
            Log.Add "  Restored original SQL for " & strQueryName, Options.ShowDebug
        Else
            Log.Add "  Couldn't get original SQL query for " & strQueryName
        End If

Result:

.sql file:
PARAMETERS pExportID Long;
UPDATE tbl_export_frachtkosten INNER JOIN tbl_frachtkosten ON (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) AND (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID) SET tbl_export_frachtkosten.Fracht_eu = [tbl_frachtkosten].[Fracht_eu], tbl_export_frachtkosten.Verzollung_eu = [tbl_frachtkosten].[Verzollung_eu]
WHERE ((([ExportID])=[pExportID]));

==== Before import of .sql
Imported from LoadComponentFromText:
PARAMETERS pExportID Long;
UPDATE tbl_export_frachtkosten INNER JOIN tbl_frachtkosten ON (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) AND (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID) SET tbl_export_frachtkosten.Fracht_eu = [tbl_frachtkosten].[Fracht_eu], tbl_export_frachtkosten.Verzollung_eu = [tbl_frachtkosten].[Verzollung_eu]
WHERE ((([ExportID])=[pExportID]));

==== Before import of .sql (after querydefs.refresh)
Imported from LoadComponentFromText:
PARAMETERS pExportID Long;
UPDATE tbl_export_frachtkosten INNER JOIN tbl_frachtkosten ON (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) AND (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID) SET tbl_export_frachtkosten.Fracht_eu = [tbl_frachtkosten].[Fracht_eu], tbl_export_frachtkosten.Verzollung_eu = [tbl_frachtkosten].[Verzollung_eu]
WHERE ((([ExportID])=[pExportID]));

==== After import of .sql
Imported from LoadComponentFromText:
PARAMETERS pExportID Long;
UPDATE tbl_export_frachtkosten INNER JOIN tbl_frachtkosten ON (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) AND (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID) SET tbl_export_frachtkosten.Fracht_eu = [tbl_frachtkosten].[Fracht_eu], tbl_export_frachtkosten.Verzollung_eu = [tbl_frachtkosten].[Verzollung_eu]
WHERE ((([ExportID])=[pExportID]));

==== After import of .sql (after querydefs.refresh)
Imported from LoadComponentFromText:
PARAMETERS pExportID Long;
UPDATE tbl_export_frachtkosten INNER JOIN tbl_frachtkosten ON (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID) AND (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) SET tbl_export_frachtkosten.Fracht_eu = [tbl_frachtkosten].[Fracht_eu], tbl_export_frachtkosten.Verzollung_eu = [tbl_frachtkosten].[Verzollung_eu]
WHERE ((([ExportID])=[pExportID]));

See the difference on last SQL ON (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) will get ON (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID)

I have no idea why the SQL is changed, when the correct SQL is saved to the QueryDef.

As a workaround I changed the code to only import the SQL when needed:

            If dbs.QueryDefs(strQueryName).SQL <> strSql Then
                dbs.QueryDefs(strQueryName).SQL = strSql
                Log.Add "  Restored original SQL for " & strQueryName, Options.ShowDebug
            End If

This solved the issue and the correct SQL will stay in the QueryDef. As I said I had several queries (32 pcs out of 460 pcs) with the issue of changing the order the SQL-join and all issues are gone. BUT: 1 Query is still not correct. It has still the same issue: changing the order of the SQL-join.

[joyfullservice]

@Tanarri - Do the two different queries return different results? My speculation is that the JET query optimizer is adjusting the query based on the latest database statistics. To quote from http://www.fmsinc.com/tpapers/faster/#Queries:

"Additionally, compacting a database updates its data statistics, which can be used by a query to run faster. You can force a query to recompile (which in turn causes it to use the latest statistics) by opening it in design view, saving it, and then running it."

If the changes you are seeing are due to optimizations made by the Microsoft Access JET engine, I would recommend just letting the program do what it wants to do here. The resulting query might not be written EXACTLY as you originally created it, especially if you wrote/modified it in the SQL editor rather than the visual designer. From all that I have read, the JET engine does a very good job optimizing queries.

If building the database from source the same way on both computers produces different output in subsequent exports, that might be a potential concern. (Although in a case like this I am not sure there is a lot we can do about it.) Again, my hunch is that we might be seeing the query at different stages in the optimization process.

If building from source causes a working query to be created as a broken, non-working query, that is a serious issue that we should work towards resolving, if possible. I know that was some of the original idea behind the option to overlay the original query SQL after importing the object. In my personal experience in dealing with hundreds and hundreds of queries across dozens of databases, I have never seen this to be a problem in my own projects, but I know there are some reports of this with certain complex queries as described in #76 and #165.

[Tanarri]

Hello @joyfullservice

thanks for your information with the link http://www.fmsinc.com/tpapers/faster/#Queries

I did not know until now that by compressing the Access database also statistics are updated. With this knowledge about statistics and Optimizer I did new tests and actually found something out which could cause the rewrite of the query - perhaps by the optimizer.

All the following I did on my source database on my PC1. All in the same database. In my source database on PC1 the following query is stored:

PARAMETERS pExportID Long;
UPDATE tbl_export_frachtkosten INNER JOIN tbl_frachtkosten ON (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) AND (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID) SET tbl_export_frachtkosten.Fracht_eu = [tbl_frachtkosten].[Fracht_eu], tbl_export_frachtkosten.Verzollung_eu = [tbl_frachtkosten].[Verzollung_eu]
WHERE (((tbl_export_frachtkosten.[ExportID])=[pExportID]));

1) I opened the query in design view and saved it, then opened the query again and looked at the SQL command. --> the SQL command remained the same.

2) I opened the query again, switched to SQL view and saved and then opened the query again and looked at the SQL command. --> the SQL command has changed! New:

PARAMETERS pExportID Long;
UPDATE tbl_export_frachtkosten INNER JOIN tbl_frachtkosten ON (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID) AND (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) SET tbl_export_frachtkosten.Fracht_eu = [tbl_frachtkosten].[Fracht_eu], tbl_export_frachtkosten.Verzollung_eu = [tbl_frachtkosten].[Verzollung_eu]
WHERE (((tbl_export_frachtkosten.[ExportID])=[pExportID]));

--> Look at after ON

3) I opened the query a third time, switched to SQL view and saved, then reopened the query and looked at the SQL command. --> the SQL command has changed again!

PARAMETERS pExportID Long;
UPDATE tbl_export_frachtkosten INNER JOIN tbl_frachtkosten ON (tbl_export_frachtkosten.Land = tbl_frachtkosten.Land) AND (tbl_export_frachtkosten.Preisliste_Handel = tbl_frachtkosten.tpl_ID) SET tbl_export_frachtkosten.Fracht_eu = [tbl_frachtkosten].[Fracht_eu], tbl_export_frachtkosten.Verzollung_eu = [tbl_frachtkosten].[Verzollung_eu]
WHERE (((tbl_export_frachtkosten.[ExportID])=[pExportID]));

--> Look at after ON

Result: When I save the query in the design view, it persists! But when I save the query in the SQL command view, it changes. It changes between "2" and "3" every time I save it in the SQL command view

[hecon5]

@Tanarri, I've noticed this, too. I've spent some time yelling into the abyss about this and / or searching google results page 3 about this, to no avail. The other big one is that in my VBE, case changes constantly, leading me to think hundreds of files changed...when in fact nothing changed except VBE decided that I didn't actually want whatever case I had previously.

I have not noticed any changes on the actual results from the queries, so I eventually let myself let go of this. This is also a contributing factor to me dynamically generating nearly all queries dynamically JIT, instead of letting them compile prior.

Except a very small set of cases, I've not noticed any performance hit, and in many cases they're now faster. For the few queries that "need" to be pre-compiled (joins on joins on sub-select, etc.), I just built a server-side view or stored procedure to handle those, and let SQL Server handle the lifting. I (thankfully) have access to the SQL Server definition, so that gives me a leg up, I realize.

The other (not insignificant) upside of this is that my code base shrank ... considerably. The single downside is that now that I'm using "standard" stored procedures, the SQL definition changes every time I export. But, after an initial commit for the placeholder query to be built upon re-build of the database from code, I ignore ignore any changes.

[joyfullservice]

The other (not insignificant) upside of this is that my code base shrank ... considerably. The single downside is that now that I'm using "standard" stored procedures, the SQL definition changes every time I export. But, after an initial commit for the placeholder query to be built upon re-build of the database from code, I ignore ignore any changes.

@hecon5 - What I do for this is add a Run Sub Before Export call that updates the SQL for each of my dynamic queries to a consistent value. This keeps my code exports consistent even though the dynamic queries are constantly changing.

For example, I might have a pass-through query with the following: exec GetInvoiceDetail 6458315

In my custom sub, I set this to: exec GetInvoiceDetail 123456

Then, no matter which invoice I happened to be viewing last, it always exports using the same invoice number, eliminating the constant code changes.

Hope that helps!

[hecon5]

oh, that's brilliant! Don't know why I didn't think of that...

Thanks!

[Tanarri]

Hello all,

In principle, I also like the idea of setting the pass-through queries in "Run Sub Before Export" back to an initial value.

I have 3 pass-through queries in my project, which I use. I pass parameters to them, so I don't need this technique for them. All the other approx. 1200 queries are Access queries to which I pass parameters via VBA code. For my part, I don't like to switch to pass-through queries here - but this is my opinion.

So it looks like there is no solution to the problem reported here at the moment. Except for a small workaround that I wrote above as a possibility. What do you think? Would it help to include a check when setting the SQL that the SQL query is only set if it is different?

            If dbs.QueryDefs(strQueryName).SQL <> strSql Then
                dbs.QueryDefs(strQueryName).SQL = strSql
                Log.Add " Restored original SQL for " & strQueryName, Options.ShowDebug
            End If

[hecon5]

For my part, I'd rather not alter the "Access JET Optimized" queries; especially where I have novice developers who aren't going to be certain as to what is happening. In my experience, they pretty much ignore any notices on the import, and just commit willy-nilly, not least because I don't want build time go go up, and as soon as you reset the SQL, it's going to rebuild the query, which takes more time.

Having the exported query change and having a developer's pull request reflect that change ensures we know what the query changed into, and if that change makes sense and doesn't alter the results, then IMO, I'd rather change the query to reflect that 'more optimal' state than what was originally proposed.

However: if that query is consistently changing back and forth (eg like VBE likes to do with case handling (FunctionName > functionName > functionname > FunctionName)), then perhaps we need to spend some more time figuring out what's happening and how to best address it.

[joyfullservice]

Thank you all for the discussion and research on this issue! Let me go ahead and summarize what I understand to be the issue.

Summary

Microsoft Access queries can be stored in two internal formats, either as a compiled or uncompiled query. These produce significantly different output in the export .bas source files, and sometimes slight differences in the exported .sql files. Whether the query was saved in a compiled state can depend on whether it was last edited in the visual designer, or whether it was changed by directly editing the SQL.

Plan of Action

As we look at how to work with this behavior, let me point out a couple considerations:

• The overall purpose and goal of this add-in is to export the current state of the database as source files. (And to be able to rebuild the database from those source files.) The add-in is not designed to change the way Access works internally, or to make any "fixes" or changes to the host database, other than what is necessary to perform its purpose of exporting/importing source code.
• The add-in also provides "hooks" where you can call custom code before or after export, and before or after build. These are great places to integrate custom code in your project to make other changes to your database based on your specific needs and internal workflow. There is nothing wrong with changing your database, in fact, that's the whole point of automation! 😄 Where the scope of this add-in ends, you can pick up from there and do anything you want to your database objects.

My analysis of this issue is that we are dealing with an internal behavior of Microsoft Access that is working as it was designed. The add-in is also working as intended, faithfully exporting and importing based on the current state of the query objects. Any additional changes to the queries to maintain consistency at the source level should be done in either of the following ways:

• Always use the query designer to make changes to the queries, so that they are saved in a consistent "compiled" state.
• Or, add a call to a custom VBA routine that analyzes the queries before export, opening and saving them using the designer, or in SQL to ensure that they are always saved in the same way before exporting.

Based on this, I would propose that we close this issue. Let me know if you have any additional feedback. 😄