search

x-ware-ltd / access-scc-addin

Microsoft Access SCC Addin
Apache License 2.0
2 stars 4 forks source link

Revisit saving queries #57

Open gilbertbw opened 4 years ago

gilbertbw commented 4 years ago

Migrated from internal Tasklist ref 1011:

14/06/2011 11:33:36 [BP] Did some further research into other methods of creating an SQL view as the default rather than Design View. There is nothing in the object model to allow this to be set. I checked 2 queries side by side looking at their QueryDef and also their Document in Containers("Tables"). They were identical despite having defferent default views.

I also check the MSysObjects table which also had identical records for both.

I did however discover where the settings are stored within Access. It's in the MSysQueries table whereby the Query opening in Design View has additional records in this table to define what the design grid looks like. The Query in SQL view has minimal rows in this table. Unfortunatley the Owner of this table is user 'Engine' and so records cannot be manipulated directly. I tried Delete and Insert without success.

Also did searches on the Internet and found no solution even close to as good as the one we already have.

09/07/2009 10:46:22 [SF] Further notes. If you create/modify a query in SQL mode, access will note that the query is in SQL mode not query builder mode, and reopening it will show in SQL view. Some queries will be modified when switching to query builder mode, if Access thinks it can optimise it using DeMorgans theorem. This is not always acceptable, especially when calling VBA functions in the query.

When exporting the queries using SVN, they are exported in two ways: 1/ As a defintion of a query 2/ As a raw SQL

When importing a query it will import from either mode, but will always set the flag to open in query designer mode, so the query is fine up until the query is opened and it then "optimised".

There is a fix in the current release of the SVN Tool which relies on form focus to modify the default behaviour of raw SQL queries and set the flag, but it does require you to step away from the computer, as you cannot use any other applications whilst this is happening.

When exporting a query Access will try to parse/optimise it. Certain SQLs need to be kept as the pure SQL and not parsed before saving. The current work arround involves looking at window focus to prevent the SQL being parsed. This means that if a user performs other tasks whilst exporting a query the export will fail. An example of SQL which causes a problem is: