OutputTo & Crosstab Queries

[Guest]

I had a heck of a time trying to figure out why this error occurred, and now
that I have a work around solution, I have to ask: Why did this happen in the
first place?

Following is the situation: I had a simple crosstab query saved in an Access
2000 database. The SQL was fine, I could view the results with no issues.
However, if I tried to use the DoCmd.OutputTo command in my code to export
the results of this query into Excel, I received the following error message:
Error 2329: To create a crosstab query, you must specify one or more Row
Heading(s) options, one Column Heading option, and one Value option.

Everything was correct, and I poured many unnecessary hours trying to figure
out why this would not work. I stumbled on my work around seemingly by
accident when trying to recreate the error with different data in a different
database.

Essentially, when I wrote the query I did it in the Access SQL view.
However, if I change that same query to Design view, and hit Save, the
OutputTo command runs with absolutely no problems, and I get my results in
Excel. If I open the query back up, switch to SQL view, and hit Save,
OutputTo resorts to the above error message again. The only change was what
view Access displayed my query when I clicked Save.

This boggles me greatly, and the only reasoning I can come up with is an
Access glitch in how it saves the query. Thankfully I'm up and working now,
but I want to understand why I got this error, and determine if there is a
more fundamental problem I may be overlooking. Does anyone have more solid
evidence to explain this issue?

 

[Allen Browne]

Some possiblities:

1. SQL
Access actually changed the text of the SQL. It tends to do that, e.g.
adding lots of brackets, messing with the WHERE clause (particularly with
ORs), and so on. The result may have disambiguated something.

2. Execution plan
When you save a query, Access saves the execution plan. Re-saving may have
saved a different execution plan. If so, it works at present but may mess up
again in the future.

3. Name confusion
If you have not unchecked the boxes under:
Tools | Options | General | Name AutoCorrect
Access gets confused about the names of things. For any field name, caption,
table name or alias that was used in the past, the query can decide to refer
to the wrong thing.

Additionally, it can get confused if any of the reserved names in this list
are used:
http://allenbrowne.com/AppIssueBadWord.html

 

[Guest]

Thanks for the response Allen. I anticipated that Access was modifying the
SQL with unnecessary brackets, and can safely discount that as a possibility.
I'll do more research on your comments about Execution Plan - from the way
you describe it, it sounds like this may have been what was causing the
problem. Thanks again!

 

[Allen Browne]

Okay, some links for the query plan in Access:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacbk02/html/odc_4009c15.asp
http://builder.com.com/5100-6388-5064388.html
http://msdn.microsoft.com/archive/d...MicrosoftAccessMicrosoftJetDatabaseEngine.asp