Saving complex query blows up Access 2002!

[Mark Burns]

Msft / Gurus:
OK, I have this query that RUNS, but Access can't save it without blowing
up.
(I was able to save the query by creating it directly with a SQL string and
DAO's
CreateQueryDef method.)
The Query will RUN just fine, but Access XP BLOWS UP if I try and SAVE the
query!
Each query (INCLUDING for the "outermost" one) works perfectly as
advertised.
Question: Why the heck does this blow up?

Crash Data:
Error Signature:
AppName: msaccess.exe AppVer: 10.0.4302.0 ModName: unknown
ModVer: 0.0.0.0 Offset: 0063015f

Exception Information:
Code: 0x000008d0 Flags: 0x00000000
Record: 0x00000000c0000005 Address:0x0000000000000000
<tons of other error details omitted>

Query Details:
The query is a rather complex query consisting of:
Outermost query (Where it blows up upon trying to SAVE the query):
2 Inner-joined Tables inner-joined to a Crosstab Query with 1 Date
Parameter

PARAMETERS [Balance AsOf Date:] DateTime;
SELECT HCILookupSchoolsList.Location, [StudentBackground].[Last] & ", " &
[StudentBackground].[FirstName] AS Name, [HCIStudentAR4DocTotalBalances Aged
AsOf Query_Crosstab].SSN, [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab].SeqNo, [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab].Current, [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab].[Over 30], [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab].[Over 60], [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab].[Over 90], [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab].[Total Of Balance]
FROM (StudentBackground INNER JOIN [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab] ON (StudentBackground.SeqNo = [HCIStudentAR4DocTotalBalances
Aged AsOf Query_Crosstab].SeqNo) AND (StudentBackground.SSN =
[HCIStudentAR4DocTotalBalances Aged AsOf Query_Crosstab].SSN)) INNER JOIN
HCILookupSchoolsList ON StudentBackground.AccountNo =
HCILookupSchoolsList.AccountNo
ORDER BY StudentBackground.AccountNo, StudentBackground.Last,
StudentBackground.FirstName, [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab].SSN, [HCIStudentAR4DocTotalBalances Aged AsOf
Query_Crosstab].SeqNo;


The Query: HCIStudentAR4DocTotalBalances Aged AsOf Query_Crosstab
A Crosstab query of a complex query with 1 date parameter (the parameter
passes througgh to the inner query)

PARAMETERS [Balance AsOf Date:] DateTime;
TRANSFORM Sum([HCIStudentAR4DocTotalBalances Aged AsOf Query].Balance) AS
SumOfBalance
SELECT [HCIStudentAR4DocTotalBalances Aged AsOf Query].SSN,
[HCIStudentAR4DocTotalBalances Aged AsOf Query].SeqNo,
Sum([HCIStudentAR4DocTotalBalances Aged AsOf Query].Balance) AS [Total Of
Balance]
FROM [HCIStudentAR4DocTotalBalances Aged AsOf Query]
GROUP BY [HCIStudentAR4DocTotalBalances Aged AsOf Query].SSN,
[HCIStudentAR4DocTotalBalances Aged AsOf Query].SeqNo
ORDER BY [HCIStudentAR4DocTotalBalances Aged AsOf Query].SSN,
[HCIStudentAR4DocTotalBalances Aged AsOf Query].SeqNo
PIVOT [HCIStudentAR4DocTotalBalances Aged AsOf Query].C369;

The Query [HCIStudentAR4DocTotalBalances Aged AsOf Query]:
A complex query with 6 tables, inner and outer joins, and a UNION query
inner joined as well.

SELECT HCIStudentDocs.DocumentID, HCIStudentDocs.SSN, HCIStudentDocs.SeqNo,
HCIStudentDocs.DocTotalDue, HCIStudentDocs.DocTotalRecvd,
HCIStudentDocs.AllAmtsRecvd, HCIStudentDocs.Note,
Sum(IIf([DebitOrCredit]="D",[Amount],0)) AS DebitAmount,
Sum(IIf([DebitOrCredit]="C",[Amount],0)) AS CreditAmount,
[DebitAmount]-[CreditAmount] AS Balance, Max(T3T4ARUNION.DateRecvd) AS
LastDate, DateDiff("d",Max([DateRecvd]),[Balance AsOf Date:]) AS Days,
IIf([Days]<30,"Current",IIf([days]<60,"Over 30",IIf([days]<90,"Over
60","Over 90"))) AS C369
FROM (HCIDocumentTypesInfo INNER JOIN HCIStudentDocs ON
HCIDocumentTypesInfo.DocumentTypeID = HCIStudentDocs.DocumentTypeID) INNER
JOIN ((HCIPostingStatusIDs INNER JOIN (HCILookupPostingPeriodsList RIGHT
JOIN (HCIStudentDocLine LEFT JOIN HCICorpPostingPeriods ON
HCIStudentDocLine.PostingPeriodID = HCICorpPostingPeriods.PostingPeriodID)
ON HCILookupPostingPeriodsList.PostingSpanID =
HCICorpPostingPeriods.PostingSpanID) ON HCIPostingStatusIDs.PostingStatusID
= HCIStudentDocLine.PostingStatusID) INNER JOIN T3T4ARUNION ON
HCIStudentDocLine.TransactionID = T3T4ARUNION.TransactionID) ON
HCIStudentDocs.DocumentID = HCIStudentDocLine.DocumentID
WHERE (((HCIDocumentTypesInfo.Abbreviation)="AR4") AND
((HCIPostingStatusIDs.PostingStatusText)<>"CANCELLED") AND
((T3T4ARUNION.Recvd)=True) AND ((T3T4ARUNION.DateRecvd)<=[Balance AsOf
Date:]))
GROUP BY HCIStudentDocs.DocumentID, HCIStudentDocs.SSN,
HCIStudentDocs.SeqNo, HCIStudentDocs.DocTotalDue,
HCIStudentDocs.DocTotalRecvd, HCIStudentDocs.AllAmtsRecvd,
HCIStudentDocs.Note
ORDER BY HCIStudentDocs.SSN, HCIStudentDocs.SeqNo;

The inner-joined UNION Query:
SELECT Title3ItemizationOfFinancing.SSN, Title3ItemizationOfFinancing.SeqNo,
Title3ItemizationOfFinancing.TransactionID,
Title3ItemizationOfFinancing.FinanceCategory,
Title3ItemizationOfFinancing.AmountBilled AS NetAmount,
Title3ItemizationOfFinancing.DateDue, Title3ItemizationOfFinancing.Recvd,
Title3ItemizationOfFinancing.ActualAmount,
Title3ItemizationOfFinancing.DateRecvd,
HCIPostingStatusIDs.PostingStatusText
FROM Title3ItemizationOfFinancing INNER JOIN HCIPostingStatusIDs ON
Title3ItemizationOfFinancing.PostingStatusID =
HCIPostingStatusIDs.PostingStatusID
UNION SELECT ItemizationOfFinancing.SSN, ItemizationOfFinancing.SeqNo,
ItemizationOfFinancing.TransactionID,
ItemizationOfFinancing.FinanceCategory, ItemizationOfFinancing.NetAmount,
ItemizationOfFinancing.DateDue, ItemizationOfFinancing.Recvd,
ItemizationOfFinancing.ActualAmount, ItemizationOfFinancing.DateRecvd,
HCIPostingStatusIDs.PostingStatusText
FROM ItemizationOfFinancing INNER JOIN HCIPostingStatusIDs ON
ItemizationOfFinancing.PostingStatusID = HCIPostingStatusIDs.PostingStatusID
ORDER BY TransactionID, SSN, SeqNo, FinanceCategory;

- Mark

 

[Michael Cheng]

Hi, Mark

From your description, however, I'm unsure of what the problem is

Would you please try these:
First of all, could you just create a simple query such as "SELECT * FROM
StudentBackground", could you run it to get the proper answer and save it?
If you could not save it and it BLOWS UP, there maybe some corruptions in
your table.

Secondly, make a new database file, in which just import the tables your
complex query need (you can use File -> Get External Data -> Import ), try
to run and save your complex query to see whether it will BLOWS UP. In this
case, there maybe some corruptions in your mdb file.

Any detailed information that you can provide will be most helpful in
pinpointing the cause of this problem and finding a solution.

Thanks

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security This posting is provided "as is"
with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Mark Burns]

Hi, Mark

From your description, however, I'm unsure of what the problem is

Would you please try these:
First of all, could you just create a simple query such as "SELECT * FROM
StudentBackground", could you run it to get the proper answer and save it?

Yup - no problem. Database corruption problems were one of my first thoughts
when Access stared crashing on me.

That's not it.
I did the all the:
"Can I save _any_ query?"
"If I make a new .mdb and inport everything and then try to save this
query, will it work?"
(Answer: no)

All I can further suggest is that I send you a copy of our .mdb and you see
for yourself how/why Access crashes.

 

[Michael Cheng]

Hi, Mark

You could send you mdb to (e-mail address removed) , However, Please keep it
as small as possible, please give me some instructions to reproduce the
problem you meet and please tell me your needs as much aas possible. as
more detailed information that you can provide, it will be closer for us to
make clear of the causes and resolutions.

Moreover, the error maybe cause by the length of your tables, such as
[HCIStudentAR4DocTotalBalances Aged AsOfQuery_Crosstab]. You'd better try
to assign an alias the first time you quoted them. As too long table name
may cause internal problems in Jet Engine. So you could try this again.

Hope this help, and I am waiting for your mdb files.


Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Mark Burns]

Hi, Mark

You could send you mdb to (e-mail address removed) , However, Please keep it
as small as possible, please give me some instructions to reproduce the
problem you meet and please tell me your needs as much aas possible. as
more detailed information that you can provide, it will be closer for us to
make clear of the causes and resolutions.

Moreover, the error maybe cause by the length of your tables, such as
[HCIStudentAR4DocTotalBalances Aged AsOfQuery_Crosstab]. You'd better try
to assign an alias the first time you quoted them. As too long table name
may cause internal problems in Jet Engine. So you could try this again.

If that were correct, then why would I be able to save the query with
set oQd=currentdb.createquerydef("QueryNameHere", strTheSameSQLStringThat
MakesTheAccessQuerryDesignerUICrashHere)?

Using a data sourse having a crosstab sub-query (with a parameter) in a
report or form points out another really annoying thing about Access's
form/report editor UIs:
In either the form or report designer, whenever you touch a control/option
that normally lists field names, it must continually re-run the query (and
promting you for the parameter) in order to reflect the field names that the
crosstab generates from the data.
This is highly annoying and time-wasting! Why doesn't Access have some
mechanism to cache the field names in the report/form editor and query that
cache instead? (You could give us an option to either use this cache or
force the current
really-annoying-requery-on-the-fly-each-and-every-dang-time-you-touch-a-fiel
d-list-item behavior).

 

[Michael Cheng]

Hi, Mark

First of all, I am waiting for your mdb files, which should just contains
the table design the query used and detail description. I supposed the
query may be settled quicker for me if I could reproduce your problem.

Secondly, I cannot catch your saying "whenever you touch a control/option
that normally lists field names".How about normally lists field names?
What's more, the re-query issue you are complaining maybe the problem by
design.

Last but not the least, I wonder have you upgrade your Jet Engine into the
lastest version? You can get the lastest Jet 4.0 Service Pack 8 (SP8) at
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=2ded
dec4-350e-4cd0-a12a-d7f70a153156

and I recommand you have a look at the KB below to enhance your access
proformance.
HOW TO: Keep a Jet 4.0 Database in Top Working Condition
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303528


Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Mark Burns]

Hi, Mark

First of all, I am waiting for your mdb files, which should just contains
the table design the query used and detail description. I supposed the
query may be settled quicker for me if I could reproduce your problem.

Sorry been busy the last 2 days - will try to get it to you today.

Secondly, I cannot catch your saying "whenever you touch a control/option
that normally lists field names".How about normally lists field names?

What I meant there was that whenever you touch any option that will
pull-down a list of field names, (like for a control's data source, or a
report grouping field) the form/report's query is re-run to determine the
list of field names on-the-fly (you can easily tell this because it prompts
you for any query parameters). When the query is as complex as mine, and it
takes some time to run, this behavior becomes really annoying when
hand-designing a report or form.

What's more, the re-query issue you are complaining maybe the problem by
design.

Yes, it's that design that I'm complaining about. It could be improved
pretty easily by giving us an option to run the query once at form/report
edit startup and caching the field names rather than automatically
re-running the query ecah time a field list pull-down on a control or in the
grouping dialog is accessed.

Last but not the least, I wonder have you upgrade your Jet Engine into the
lastest version? You can get the lastest Jet 4.0 Service Pack 8 (SP8) at
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=2ded

yup.

and I recommand you have a look at the KB below to enhance your access
proformance.
HOW TO: Keep a Jet 4.0 Database in Top Working Condition
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303528

I downloaded that a long tome ago. Has it been revised recently?

 

[Michael, Cheng [MST]]

Hi Mark,

It has been four days after you said you will send your mdb files to me,
but I haven't received it them yet. Is everything alright?

Moreover, when you pull-down a list of field names, the query has to
re-run, as it doesn't have such cache mechanism for lists of field names.

Moreover, when you pull-down a list of field names, the query have has to
re-run, as it doesn't have such cache mechanism for lists of field names.

Thank you for your patience and cooperation. If you have any question or
concerns, please don't hesitate to let me know. We are here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Mark Burns]

Hi Mark,

It has been four days after you said you will send your mdb files to me,
but I haven't received it them yet. Is everything alright?

Michael,

Sorry, I've had to don the firefighting gear to handle some urgent problems
for our auditors the last few days. I may break free today long enough to
strip down a copy of the database to send you.

Moreover, when you pull-down a list of field names, the query has to
re-run, as it doesn't have such cache mechanism for lists of field names.

Yeah, I know - that's what I'm complaining about - it's be nice if there
WERE a cache for the field names - or at least an option for one to be used.
Call it a Wishlist request, Ok?

 

[Mark Burns]

Michael,

I did send you an eMail yesterday, did you get it?

 

[Michael, Cheng [MST]]

Hi, Mark

I am sorry to say that I haven't received your email yet, my email was
shown in my previous post. Would you please check it and re-send it again?

Thank you for your patience and cooperation. If you have any question or
concern, don't hesitate to let me know. We are here to be of assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Mark Burns]

Michael,

I sent you another eMail earlier today, letting you know that I can't email
you the database - it's just too big (12+ Mb .RARed down from 87Mb of 200+
Mb) - even trimmed down to the minimum, and I don't have the time to hack up
the dataset further.

If you could eMail me another means of sending you this, I'd be happy to get
this data to you ASAP.

- Mark

 

[Michael, Cheng [MST]]

Hi Mark

I have received your email, without any attachment files. You'd better
create a new mdb file and only import necessary tables and querys. I just
need a samll database design to reproduce you problems on my PC

Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.