Access changes query on save? 1

[Sendeman]

Hi All,

A strange thing just happened. I created a query that joins a few tables together and when designing it, it returned a set of 176 records. When I saved the query and opened the saved query again, it returned 0 records.

The design view still looks exactly the same and the SQL looks alright too. When I copy all the design view fields to a new empty query and I add all the required tables and relationships, this new query returns the correct amount of records (176) again. That is, until I save it. When I save it and open this new query again, it also returns zero records.

It seems Access does 'something' to my query which makes it slightly corrupt. It still looks ok in Design and SQL-view, but doesn't return any records anymore.

Has anyone here ever noticed similar behavior in MS Access (2007)? I am at a loss to understand what Access is doing to the query and to how to solve this. Maybe I could perform the query in two steps? But still: before saving it works fine...

Any help will be greatly appreciated.

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[MajP]

When you save a query it gets compiled and optimized, which obviously is not happening correctly. Therefore it works in the uncompiled state. This can change the order in when tables are sorted, joined, and filtered. So for example, if the filter takes place before the join then no records may get returned.

Please post the SQL.

http://support.microsoft.com/kb/209126

 

[Sendeman]

Thanks for your reply! That's sort of what I thought was happening. Is there anything I can do about the order in which the query is compiled?

This is the SQL code for it:

INSERT INTO Interval_BM ( BhId, DatMons, X1, Y1, Z1, X2, Y2, Z2, Comment, [Value], Depth1, Depth2, IntervalTypeId )
SELECT tbl_tmp_Locaties_vs_ProjectenIDs.[BhId] AS BhId, [itwbmwa0 - Watermonsters].[DATUM] AS DatMons, [tbl_tmp_Locaties_vs_ProjectenIDs]![Easting] AS X1, [tbl_tmp_Locaties_vs_ProjectenIDs]![Northing] AS Y1, IIf(IsNull([tbl_tmp_Locaties_vs_ProjectenIDs]![Elevation])=False,[tbl_tmp_Locaties_vs_ProjectenIDs]![Elevation]-([FILTERVAN]/100)) AS Z1, [tbl_tmp_Locaties_vs_ProjectenIDs]![Easting] AS X2, [tbl_tmp_Locaties_vs_ProjectenIDs]![Northing] AS Y2, IIf(IsNull([tbl_tmp_Locaties_vs_ProjectenIDs]![Elevation])=False,[tbl_tmp_Locaties_vs_ProjectenIDs]![Elevation]-([FILTERTOT]/100)) AS Z2, 'Analyse van boormanager watermonster ' & [itwbmwa0 - Watermonsters]![IDWATERM] & ' (' & [itwbmwa0 - Watermonsters]![WATERMNAAM] & ')' AS Comment, IIf([REF]='D',-[MEETW],[MEETW]) AS [Value], [FILTERVAN]/100 AS Depth1, [FILTERTOT]/100 AS Depth2, IntervalType_BM.[Id] AS IntervalTypeId
FROM (((((tbl_tmp_Locaties_vs_ProjectenIDs LEFT JOIN [itwbmbo0 - Boringen] ON (tbl_tmp_Locaties_vs_ProjectenIDs.Meridian=[itwbmbo0 - Boringen].IDBOORPUNT) AND (tbl_tmp_Locaties_vs_ProjectenIDs.IDPROJECT=[itwbmbo0 - Boringen].IDPROJECT)) LEFT JOIN [itwbmpe0 - Peilbuizen] ON [itwbmbo0 - Boringen].IDBOORPUNT=[itwbmpe0 - Peilbuizen].IDBOORPUNT) LEFT JOIN [itwbmwa0 - Watermonsters] ON [itwbmpe0 - Peilbuizen].IDPEILBUIS=[itwbmwa0 - Watermonsters].IDPEILBUIS) LEFT JOIN [itwbmar0 - Analyseresultaten] ON [itwbmwa0 - Watermonsters].IDWATERM=[itwbmar0 - Analyseresultaten].IDMONS) LEFT JOIN IntervalType_BM ON [itwbmar0 - Analyseresultaten].COMPONENT=IntervalType_BM.COMPONENT) LEFT JOIN bmStofcd ON IntervalType_BM.COMPONENT=bmStofcd.COMPONENT
WHERE (((tbl_tmp_Locaties_vs_ProjectenIDs.BhId) Is Not Null) And ((IIf([REF]='D',-[MEETW],[MEETW])) Is Not Null) And (([FILTERVAN]/100) Is Not Null) And (([FILTERTOT]/100) Is Not Null) And (([itwbmar0 - Analyseresultaten].MATRIX)='GW') And ((IntervalType_BM.MATRIX)='GW') And ((IntervalType_BM.Name) Like 'W*') And ((tbl_tmp_Locaties_vs_ProjectenIDs.Elevation)<>999999) And ((tbl_tmp_Locaties_vs_ProjectenIDs.IDPROJECT) Is Not Null));

Now all these table and field names will tell you nothing much, since it is a Dutch database. But can you find anything strange in the structure of the query that may cause the problems?

Thanks again in advance for any help.

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[MajP]

The normal solution is like you said to break it into multiple queries. At least base the insert on another query. Unlike sql server, as far as I know there is no way to view or modify the execution plan/order.

 

[Sendeman]

Ok thanks, I'll try a combination of other queries.

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -