Access shuts down, when i try to save this query

I can run the following query fine

However if i click on the save button MS access shuts down with no errors

i have tried on multiple machines and this is the case for all of them

TRANSFORM Count(tbl_EX_Updated_Exceptions.EMTID) AS Events
SELECT Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy') AS [Date By
Month], IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " &
[tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated by " &
[tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User]) AS [User State],
Count(tbl_EX_Updated_Exceptions.EMTID) AS [Total Events]
FROM tbl_EX_Updated_Exceptions
GROUP BY Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy'),
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1,
IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " & [tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated by "
& [tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User])
ORDER BY
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1
PIVOT tbl_EX_Updated_Exceptions.RevenueType;

Dann, I don't see anything in the query that JET should find too difficult
to handle, so I suspect there is a corruption in your database.

Try this sequence.

1. Copy the SQL out to a text document, and save it. Delete the query.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

4. Locate the file msjet40.dll on your computer (typically in
windows\system32.) Right-click and choose Properties. What is on the Version
tab? You should see 4.0.8xxx.0. The xxx digits don't matter but if you do
not see the 8, download and apply SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
or
http://support.microsoft.com/kb/239114

5. After verifying JET 4 SP8, create a new query, and paste the SQL back in.
Then try saving the query directly in SQL View, and see how you go.

The 2nd field in the GROUP BY clause looks superfluous to me, but perhaps
you have a reason for it.

Also a field named Date is not a good choice, since this is a reserved word
in VBA (for the system date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dann" wrote in message
...
I can run the following query fine

However if i click on the save button MS access shuts down with no errors

i have tried on multiple machines and this is the case for all of them

TRANSFORM Count(tbl_EX_Updated_Exceptions.EMTID) AS Events
SELECT Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy') AS [Date By
Month], IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " &
[tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated by " &
[tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User]) AS [User State],
Count(tbl_EX_Updated_Exceptions.EMTID) AS [Total Events]
FROM tbl_EX_Updated_Exceptions
GROUP BY Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy'),
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1,
IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " & [tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated
by "
& [tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User])
ORDER BY
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1
PIVOT tbl_EX_Updated_Exceptions.RevenueType;

Thanks i will give this a go , i have been trying to resolve this problem and
heres an update for anyone thats interested

Its the nested ifs that are causing the problem : can't see why
Circumstances are
create the query , run and save = no problem
Close the query
open the query , run = no problem , click save or change something (except
the if statement) and then click save = access shuts down immediatley without
errors

Very peculiar no idea why its falling over on a nested iif statement

will try your suggestions though and let all know the outcome



"Allen Browne" wrote:

Dann, I don't see anything in the query that JET should find too difficult
to handle, so I suspect there is a corruption in your database.

Try this sequence.

1. Copy the SQL out to a text document, and save it. Delete the query.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

4. Locate the file msjet40.dll on your computer (typically in
windows\system32.) Right-click and choose Properties. What is on the Version
tab? You should see 4.0.8xxx.0. The xxx digits don't matter but if you do
not see the 8, download and apply SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
or
http://support.microsoft.com/kb/239114

5. After verifying JET 4 SP8, create a new query, and paste the SQL back in.
Then try saving the query directly in SQL View, and see how you go.

The 2nd field in the GROUP BY clause looks superfluous to me, but perhaps
you have a reason for it.

Also a field named Date is not a good choice, since this is a reserved word
in VBA (for the system date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dann" wrote in message
...
I can run the following query fine

However if i click on the save button MS access shuts down with no errors

i have tried on multiple machines and this is the case for all of them

TRANSFORM Count(tbl_EX_Updated_Exceptions.EMTID) AS Events
SELECT Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy') AS [Date By
Month], IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " &
[tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated by " &
[tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User]) AS [User State],
Count(tbl_EX_Updated_Exceptions.EMTID) AS [Total Events]
FROM tbl_EX_Updated_Exceptions
GROUP BY Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy'),
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1,
IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " & [tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated
by "
& [tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User])
ORDER BY
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1
PIVOT tbl_EX_Updated_Exceptions.RevenueType;

Hi Dann,

PMFBI

I may be *way off base*, but I might also
suggest trying to write the sql w/o any
bangs (!).

in last FMS Buzz newsletter they offered
the following tip:
http://www.fmsinc.com/offers/buzz/issue5.html

***quote***
ACCESS TIP: THE ME OPERATOR

The Me operator should be used to refer to controls
on the form or report in which code is running.
In doing so, using "." rather than "!" after Me helps
catch compile time errors.
Otherwise, the errors are only discovered
when the application is executed.
For example:

Use : Me.txtLastName
Instead of : Forms!frmPerson!txtLastName
***unquote***

So what does that have to do with your situation?

in my possibly convoluted reasoning,
compiling treats "!" differently than "."
when it comes to "die gracefully"....
maybe you get lucky, maybe not...

So, you changed your "Date" field to
something like "ExceptDate," you alias
your table name, and you don't use any
brackets in your SELECT portion...

TRANSFORM Count(t.EMTID) AS Events
SELECT
Format$(t.ExceptDate,'mmmm yyyy') AS DateByMonth,

IIf(t.Closed_User Is Null,
IIf(t.Last_Updated_User Is Null,
"Current User " & t.Status_User_Current,
"Last Updated by " & t.Last_Updated_User),
"Closed by " & t.Closed_User) AS UserState,

Count(t.EMTID) AS TotalEvents
FROM tbl_EX_Updated_Exceptions AS t
.......

or try using SWITCH instead of IIF

TRANSFORM Count(t.EMTID) AS Events
SELECT
Format$(t.ExceptDate,'mmmm yyyy') AS DateByMonth,

Switch( t.Closed_User Is Null
AND t.Last_Updated_User Is Null,
"Current User " & t.Status_User_Current,
t.Closed_User Is Null
AND t.Last_Updated_User Is NOT Null,
"Last Updated by " & t.Last_Updated_User,
True, "Closed by " & t.Closed_User) AS UserState,

Count(t.EMTID) AS TotalEvents
FROM tbl_EX_Updated_Exceptions AS t
.......

Apologies again for butting in,

gary

"Dann" wrote
I can run the following query fine

However if i click on the save button MS access shuts down with no errors

i have tried on multiple machines and this is the case for all of them

TRANSFORM Count(tbl_EX_Updated_Exceptions.EMTID) AS Events
SELECT Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy') AS [Date By
Month], IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " &
[tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated by " &
[tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User]) AS [User State],
Count(tbl_EX_Updated_Exceptions.EMTID) AS [Total Events]
FROM tbl_EX_Updated_Exceptions
GROUP BY Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy'),
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1,
IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " & [tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated
by "
& [tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User])
ORDER BY
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1
PIVOT tbl_EX_Updated_Exceptions.RevenueType;

Dann,

I've seen previously seen identical behavior from Access XP.
In my case the query that did identical stuff to Access was one with
parameterized Crosstab subqueries - there _may_ have been some funly iif(...)
expressions in one or more fields of one or more subqueries as well, so
perhaps you've found the same sort of problem I've seen before.
I could reliably and repeatably crash out of Access entirely (with no debug
screens or anything) by attempting to SAVE the query again from the Access UI
(after the initial creation of it worked OK).
Putting the same exact SQL into a VB string and doing a
currentdb.createquerydef("name here", sqlexpressionstringvariabelhere)
would work fine to create the query, and I could then also do a
ExistingQueryDefObject.SQL = sqlexpressionstringvariabelhere
to update that same query without problem too.

MS was interested in the problem, but I couldn't produce an example database
small enough (even .ZIPped) to fit through their eMail limits at the time
(though they asked for it).

"Dann" wrote:

Thanks i will give this a go , i have been trying to resolve this problem and
heres an update for anyone thats interested

Its the nested ifs that are causing the problem : can't see why
Circumstances are
create the query , run and save = no problem
Close the query
open the query , run = no problem , click save or change something (except
the if statement) and then click save = access shuts down immediatley without
errors

Very peculiar no idea why its falling over on a nested iif statement

will try your suggestions though and let all know the outcome



"Allen Browne" wrote:

Dann, I don't see anything in the query that JET should find too difficult
to handle, so I suspect there is a corruption in your database.

Try this sequence.

1. Copy the SQL out to a text document, and save it. Delete the query.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

4. Locate the file msjet40.dll on your computer (typically in
windows\system32.) Right-click and choose Properties. What is on the Version
tab? You should see 4.0.8xxx.0. The xxx digits don't matter but if you do
not see the 8, download and apply SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
or
http://support.microsoft.com/kb/239114

5. After verifying JET 4 SP8, create a new query, and paste the SQL back in.
Then try saving the query directly in SQL View, and see how you go.

The 2nd field in the GROUP BY clause looks superfluous to me, but perhaps
you have a reason for it.

Also a field named Date is not a good choice, since this is a reserved word
in VBA (for the system date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dann" wrote in message
...
I can run the following query fine

However if i click on the save button MS access shuts down with no errors

i have tried on multiple machines and this is the case for all of them

TRANSFORM Count(tbl_EX_Updated_Exceptions.EMTID) AS Events
SELECT Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy') AS [Date By
Month], IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " &
[tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated by " &
[tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User]) AS [User State],
Count(tbl_EX_Updated_Exceptions.EMTID) AS [Total Events]
FROM tbl_EX_Updated_Exceptions
GROUP BY Format$([tbl_EX_Updated_Exceptions].[Date],'mmmm yyyy'),
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1,
IIf([tbl_EX_Updated_Exceptions]![Closed_User] Is
Null,IIf([tbl_EX_Updated_Exceptions]![Last_Updated_User] Is Null,"Current
User " & [tbl_EX_Updated_Exceptions]![Status_User_Current],"Last Updated
by "
& [tbl_EX_Updated_Exceptions]![Last_Updated_User]),"Closed by " &
[tbl_EX_Updated_Exceptions]![Closed_User])
ORDER BY
Year([tbl_EX_Updated_Exceptions].[Date])*12+DatePart('m',[tbl_EX_Updated_Exceptions].[Date])-1
PIVOT tbl_EX_Updated_Exceptions.RevenueType;

"Gary Walter" wrote:
Hi Dann,

PMFBI

I may be *way off base*, but I might also
suggest trying to write the sql w/o any
bangs (!).

in last FMS Buzz newsletter they offered
the following tip:
http://www.fmsinc.com/offers/buzz/issue5.html

***quote***
ACCESS TIP: THE ME OPERATOR

The Me operator should be used to refer to controls
on the form or report in which code is running.
In doing so, using "." rather than "!" after Me helps
catch compile time errors.
Otherwise, the errors are only discovered
when the application is executed.
For example:

Use : Me.txtLastName
Instead of : Forms!frmPerson!txtLastName
***unquote***

So what does that have to do with your situation?

in my possibly convoluted reasoning,
compiling treats "!" differently than "."
when it comes to "die gracefully"....
maybe you get lucky, maybe not...

Gary,
Well you're both right and wrong there.
To properly understand ME and how it differs from ! you need to think of the
code's execution context. All of the VBA code written within a form executes
within that form's execution context. Queries do not (necessarily, at least)
have to execute within any form's context. Remember, you can open a query
from the main database window - totally outside the form that may be where it
(usually) gets called. In that context "Me." means nothing as there is no
"me" object currently defined (as in, no form instance instantiating the code
class for execution within it's own instance - and this is what "Me." refers
to). So, using Me.anything in a query expression will not work, and the !
syntax is what is used to make "absolute" references ("Access!" or
"Application!" is always implied in front of "forms!" or "reports!"-type
expressions) in queries.

Now, reread that first sentence of the FMS item you spoke of:
The Me operator should be used to refer to controls
on the form or report **in which code is running.**
(emphasis mine)
got it?

So, you changed your "Date" field to
something like "ExceptDate," you alias
your table name, and you don't use any
brackets in your SELECT portion...

Yup - NEVER use "Date" - I even go so far as to use VBA.DATE when using the
date function in code - eliminated ANY chance of ambiguity (and promotes code
readability and clarity of intent IMO),

TRANSFORM Count(t.EMTID) AS Events
SELECT
Format$(t.ExceptDate,'mmmm yyyy') AS DateByMonth,

IIf(t.Closed_User Is Null,
IIf(t.Last_Updated_User Is Null,
"Current User " & t.Status_User_Current,
"Last Updated by " & t.Last_Updated_User),
"Closed by " & t.Closed_User) AS UserState,

Count(t.EMTID) AS TotalEvents
FROM tbl_EX_Updated_Exceptions AS t
.......

or try using SWITCH instead of IIF

TRANSFORM Count(t.EMTID) AS Events
SELECT
Format$(t.ExceptDate,'mmmm yyyy') AS DateByMonth,

Switch( t.Closed_User Is Null
AND t.Last_Updated_User Is Null,
"Current User " & t.Status_User_Current,
t.Closed_User Is Null
AND t.Last_Updated_User Is NOT Null,
"Last Updated by " & t.Last_Updated_User,
True, "Closed by " & t.Closed_User) AS UserState,

Count(t.EMTID) AS TotalEvents
FROM tbl_EX_Updated_Exceptions AS t
.......

Apologies again for butting in,

don't appologize for butting in, that's what these newsgroups are all about!

Hi Mark,

This is what I think I know....

the following are *snips* from

http://msdn.microsoft.com/library/de...dc_4009c15.asp

****quote*****

Queries are compiled and optimized the first time you run the query.

They are not recompiled until you resave and rerun the query.

You shouldn't save the query after running it
or it may be saved in an uncompiled state.

You can force recompilation by opening the query in design mode,
saving it, and then reexecuting it.

-----------------

Before Jet can optimize a query, it must parse the SQL statement that
defines the query
and bind the names referenced in the query to columns in the underlying
tables.
The Jet query engine compiles the SQL string into an internal query object
definition format,
replacing common parts of the query string with tokens.
The internal format can be likened to an inverted tree:
the query's result set sits at the top of the tree (the tree's root),
and the base tables are at the bottom (the leaves).

Query definitions are parsed into distinct elements when compiled.
These elements include:

Base tables
Output columns (the fields that will appear in the query's result set)
Restrictions (in QBE, the criteria; in SQL, WHERE clause elements)
Join columns (in QBE, the lines connecting two tables; in SQL, the fields in
the JOIN clause)
Sort columns (in QBE, sort fields; in SQL, the fields in the ORDER BY
clause)

The query optimizer {..chooses..} the optimum query execution strategy for
the compiled query tree.

After a query has been compiled and optimized by the Jet query optimizer,
two additional steps are taken prior to the execution of the query.

For queries involving external data sources, the remote post-processor
determines
how much of a query can be sent to the back end for processing by the
database server application.

The remote post-processor identifies those parts of the query tree that can
be satisfied
by server queries and generates the server SQL strings for each remote
query.

Finally, the post-processor takes the compiled query tree
and moves it to a new, cleaner, and smaller execution segment.
This is the final step prior to query execution.

****unquote****

While Dann said nothing about compacting,
that also will flag all queries to be recompiled
the next time the query is executed.

I do not know what further complications are involved
when the query is a crosstab. If I had the time, I would
turn on the registry flag for logging the plan using a crosstab
with IIF's using bangs.....

The "Me part" of the FMS tip had nothing to do
with my wild-a###ed speculation.

What interested me was that a bang might cause
a parse/compile process to not die gracefully, i.e.,
Access just shuts down.

**off topic***
Curiously, the only time (in the last few years) that I have had
"Access just shut down" involved "Me!xxx" (I know Me has
nothing to do with this query problem).

At home I use Office XP Developer on WinXP Pro.
At work, all our systems are Win2K Pro with either
Office 2K Pro or 2000 runtime. My work computer runs
Office 2K Developer.

I was bringing dbs home to work on them. They had code
that was rewriting label captions using Me!lblxxx. I would
first open dbs up and check references, then recompile.
Then compact/repair which would bring the initial form
up, I'd click on a button, then Access would quit.

It turned out that somewhere in the process
out of all the code lines that used

Me!lblxxx.Caption

a "random" line was becoming

lblxxx.Caption

Adding back the "Me!" would cure the problem.

It did not fail the "other way," i.e., going from
XP db to 2K db.

***end of off topic***

Dann said:

Circumstances are

create the query , run and save = no problem
Close the query

{from above, I take it that query will now
be in an uncompiled state}


open the query , run = no problem ,

{which I take it that query will now
be recompiled/optimized}

click save or change something (except
the if statement) and then click save
= access shuts down immediately without errors

{which I take it that the process of
moving it back to an uncompiled
state is causing Access to choke.}

So...my wild-a###ed speculation
about compile/parsing was choking
on bang was completely wrong. It
would appear that it is choking on the
"uncompile."

Gary Walter
Ex-Microsoft Access MVP

I have the table and the query in a file zipped is 2.7mb if anyone wants it

can email or i'll send a link to my webserver later on

Dann



"Gary Walter" wrote:

Hi Mark,

This is what I think I know....

the following are *snips* from

http://msdn.microsoft.com/library/de...dc_4009c15.asp

****quote*****

Queries are compiled and optimized the first time you run the query.

They are not recompiled until you resave and rerun the query.

You shouldn't save the query after running it
or it may be saved in an uncompiled state.

You can force recompilation by opening the query in design mode,
saving it, and then reexecuting it.

-----------------

Before Jet can optimize a query, it must parse the SQL statement that
defines the query
and bind the names referenced in the query to columns in the underlying
tables.
The Jet query engine compiles the SQL string into an internal query object
definition format,
replacing common parts of the query string with tokens.
The internal format can be likened to an inverted tree:
the query's result set sits at the top of the tree (the tree's root),
and the base tables are at the bottom (the leaves).

Query definitions are parsed into distinct elements when compiled.
These elements include:

Base tables
Output columns (the fields that will appear in the query's result set)
Restrictions (in QBE, the criteria; in SQL, WHERE clause elements)
Join columns (in QBE, the lines connecting two tables; in SQL, the fields in
the JOIN clause)
Sort columns (in QBE, sort fields; in SQL, the fields in the ORDER BY
clause)

The query optimizer {..chooses..} the optimum query execution strategy for
the compiled query tree.

After a query has been compiled and optimized by the Jet query optimizer,
two additional steps are taken prior to the execution of the query.

For queries involving external data sources, the remote post-processor
determines
how much of a query can be sent to the back end for processing by the
database server application.

The remote post-processor identifies those parts of the query tree that can
be satisfied
by server queries and generates the server SQL strings for each remote
query.

Finally, the post-processor takes the compiled query tree
and moves it to a new, cleaner, and smaller execution segment.
This is the final step prior to query execution.

****unquote****

While Dann said nothing about compacting,
that also will flag all queries to be recompiled
the next time the query is executed.

I do not know what further complications are involved
when the query is a crosstab. If I had the time, I would
turn on the registry flag for logging the plan using a crosstab
with IIF's using bangs.....

The "Me part" of the FMS tip had nothing to do
with my wild-a###ed speculation.

What interested me was that a bang might cause
a parse/compile process to not die gracefully, i.e.,
Access just shuts down.

**off topic***
Curiously, the only time (in the last few years) that I have had
"Access just shut down" involved "Me!xxx" (I know Me has
nothing to do with this query problem).

At home I use Office XP Developer on WinXP Pro.
At work, all our systems are Win2K Pro with either
Office 2K Pro or 2000 runtime. My work computer runs
Office 2K Developer.

I was bringing dbs home to work on them. They had code
that was rewriting label captions using Me!lblxxx. I would
first open dbs up and check references, then recompile.
Then compact/repair which would bring the initial form
up, I'd click on a button, then Access would quit.

It turned out that somewhere in the process
out of all the code lines that used

Me!lblxxx.Caption

a "random" line was becoming

lblxxx.Caption

Adding back the "Me!" would cure the problem.

It did not fail the "other way," i.e., going from
XP db to 2K db.

***end of off topic***

Dann said:

Circumstances are

create the query , run and save = no problem
Close the query

{from above, I take it that query will now
be in an uncompiled state}


open the query , run = no problem ,

{which I take it that query will now
be recompiled/optimized}

click save or change something (except
the if statement) and then click save
= access shuts down immediately without errors

{which I take it that the process of
moving it back to an uncompiled
state is causing Access to choke.}

So...my wild-a###ed speculation
about compile/parsing was choking
on bang was completely wrong. It
would appear that it is choking on the
"uncompile."

Gary Walter
Ex-Microsoft Access MVP

"Dann" wrote
I have the table and the query in a file zipped is 2.7mb if anyone wants it

can email or i'll send a link to my webserver later on

Hi Dann,

You may send link to me if you want
...(remove please and no and spam
from my email address)

Busy at work so response back probably
won't be immediate if that makes a difference.
But that work is what pays my bills.

To be clear...I am just some schmo volunteer
from MidWest US with no connection to MS
(nor MVP's anymore). But I will look at it
(when I can).

Hopefully you have tried Allen's advice,
plus you tried the 2 queries I suggested?

gary

Gary,

Heh. Kudos to you for not smacking me down _hard_.

I didn't realize I was talking to-/trying to "edumacate"- an (ex-)Access
MVP...

I'll just go sit quietly over here in a corner now and read that link you
just sent until I grok it completely....

(some other comment inline below)

"Gary Walter" wrote:

Hi Mark,

This is what I think I know....

the following are *snips* from

http://msdn.microsoft.com/library/de...dc_4009c15.asp

****quote*****

Queries are compiled and optimized the first time you run the query.

They are not recompiled until you resave and rerun the query.

You shouldn't save the query after running it
or it may be saved in an uncompiled state.

You can force recompilation by opening the query in design mode,
saving it, and then reexecuting it.

-----------------

Before Jet can optimize a query, it must parse the SQL statement that
defines the query
and bind the names referenced in the query to columns in the underlying
tables.
The Jet query engine compiles the SQL string into an internal query object
definition format,
replacing common parts of the query string with tokens.
The internal format can be likened to an inverted tree:
the query's result set sits at the top of the tree (the tree's root),
and the base tables are at the bottom (the leaves).

Query definitions are parsed into distinct elements when compiled.
These elements include:

Base tables
Output columns (the fields that will appear in the query's result set)
Restrictions (in QBE, the criteria; in SQL, WHERE clause elements)
Join columns (in QBE, the lines connecting two tables; in SQL, the fields in
the JOIN clause)
Sort columns (in QBE, sort fields; in SQL, the fields in the ORDER BY
clause)

The query optimizer {..chooses..} the optimum query execution strategy for
the compiled query tree.

After a query has been compiled and optimized by the Jet query optimizer,
two additional steps are taken prior to the execution of the query.

For queries involving external data sources, the remote post-processor
determines
how much of a query can be sent to the back end for processing by the
database server application.

The remote post-processor identifies those parts of the query tree that can
be satisfied
by server queries and generates the server SQL strings for each remote
query.

Finally, the post-processor takes the compiled query tree
and moves it to a new, cleaner, and smaller execution segment.
This is the final step prior to query execution.

****unquote****

While Dann said nothing about compacting,
that also will flag all queries to be recompiled
the next time the query is executed.

I do not know what further complications are involved
when the query is a crosstab. If I had the time, I would
turn on the registry flag for logging the plan using a crosstab
with IIF's using bangs.....

The "Me part" of the FMS tip had nothing to do
with my wild-a###ed speculation.

What interested me was that a bang might cause
a parse/compile process to not die gracefully, i.e.,
Access just shuts down.

**off topic***
Curiously, the only time (in the last few years) that I have had
"Access just shut down" involved "Me!xxx" (I know Me has
nothing to do with this query problem).

At home I use Office XP Developer on WinXP Pro.
At work, all our systems are Win2K Pro with either
Office 2K Pro or 2000 runtime. My work computer runs
Office 2K Developer.

I was bringing dbs home to work on them. They had code
that was rewriting label captions using Me!lblxxx. I would
first open dbs up and check references, then recompile.
Then compact/repair which would bring the initial form
up, I'd click on a button, then Access would quit.

It turned out that somewhere in the process
out of all the code lines that used

Me!lblxxx.Caption

a "random" line was becoming

lblxxx.Caption

Adding back the "Me!" would cure the problem.

It did not fail the "other way," i.e., going from
XP db to 2K db.

***end of off topic***

Dann said:

Circumstances are

create the query , run and save = no problem
Close the query

{from above, I take it that query will now
be in an uncompiled state}


open the query , run = no problem ,

{which I take it that query will now
be recompiled/optimized}

click save or change something (except
the if statement) and then click save
= access shuts down immediately without errors

IIRC, my experience with the problem is that if you just open it and change
*nothing*, and click save, it just closes the query window (whether it
actually "saves" or flags the querydef for recompilation, I do not know, but
suspect not.) ...but I could be early alzheimersmis-remembering/early
alzheimers that detail (this was 18 months ago or so).

{which I take it that the process of
moving it back to an uncompiled
state is causing Access to choke.}

So...my wild-a###ed speculation
about compile/parsing was choking
on bang was completely wrong. It
would appear that it is choking on the
"uncompile."

....why would they have any query "uncompile" at all - why not just toss the
previous compiled bits away and rebuild from scratch completely...(unless
this was a cquery compiler optimization attempt?)...? or perhaps it's that
toss-the-compiled-bits process where things are going bump?

I probably still can get hold of the 80+mbzipped example I had put
together for MS's review if you're interested. though you may have to promise
confidentiality with dome of the data (even 18-months old, some of it remains
"sensitive").

Gary Walter
Ex-Microsoft Access MVP