Application Error with complex Crosstab query

[dsurls]

I have a fairly complex crosstab query that is created via DAO code in a VB6 app. The query is saved in a Jet 4.0 database, and when I run it directly from within Access 2003 it runs fine; but when I run it from within the VB6 app, I get an Application Error which crashes the IDE:

vb6.exe - Application Error
The instruction at "0x7c910de3" referenced memory at "0xfffffff8". The memory could not be "read".

It also crashes the compiled application if I run it outside the IDE.

The SQL for the query is below. Note that the PIVOT clause is used to specify the order of the columns, which are a concatenation of two fields in a related table. The columns are specified explicitly using an "IN ()" clause. I have a suspicion that the length of that clause is causing the problem (smaller lists of columns work fine). If so, is there a way to specify the PIVOT columns using a subquery rather than explicitly listing them one by one? Do you think that could indeed be the culprit, and if not, what else could it be?

Thanks very much for your help!!

David

TRANSFORM Sum(SumValue) AS [Sum]
SELECT [Date]

FROM (SELECT Sum(SourceData.[Data Value])/Sum(SourceData.[Applied Value]) AS SumValue, [Date].Order AS [DateOrder], SourceData.[Date], SourceData.[Scenario], SourceData.[Draw] 

FROM (((SourceData LEFT JOIN [Date] ON SourceData.[Date] = [Date].[Date]) LEFT JOIN  [Scenario] ON [SourceData].[Scenario] = [Scenario].[Scenario]) LEFT JOIN  [Draw] ON [SourceData].[Draw] = [Draw].[Draw])

WHERE [SourceData].[Data Item] = 'Total System Cost'
 GROUP BY SourceData.[Date], [Date].Order, SourceData.[Scenario], SourceData.[Draw]) AS BaseData

GROUP BY [DateOrder], [Date]
ORDER BY [DateOrder]

PIVOT Left([Scenario] & ': ' & [Draw], 64) 
IN ("Base Case: 0" ,"Supply Collar - MC: 0" ,"Supply Collar - MC: 1" ,
...,   (a total of about 202 columns)
 "Supply Collar - MC: 199" ,"Supply Collar - MC: 200");

 

[dhookom]

I have not found a method for using a subquery for the column headings. Are you sure you don't approach the 255 column limit? Do you have any periods, single, or double quotes in your column headings? How about records where there is no matching Scenario value?

Duane
Hook'D on Access
MS Access MVP

 

[dsurls]

Thanks for the reply, Duane.

No, I'm sure it is not approaching the 255 column limit. And the query works fine within Access directly, so I know there are no issues with bad characters or records not matching the values.

I found a website that illustrates using subqueries within "IN (...)" clauses, so I'm going to try that out to see if it will work. Let me know if you think it will be a futile effort.

http://articles.techrepublic.com.com/5100-22-1045787.html

Thanks,

David

 

[dhookom]

I don't expect you will have much success since that article doesn't seem to address crosstab queries which are different from standard select queries.

How do you intend to use this in your VB6 app?

Duane
Hook'D on Access
MS Access MVP

 

[dsurls]

You're right, Duane -- no luck using subqueries in the IN clause.

Any other ideas? Know of any limitations on the size of the SQL string that I might be hitting, or anything else I can trap before the app crashes?

Thanks,

David

 

[dhookom]

You can search Access Help for "Access Specifications".

Duane
Hook'D on Access
MS Access MVP