Re: OutputTo & Crosstab Queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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

--
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.

"Alyindar" <Alyindar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:011B8D62-15A5-4C76-B23F-71521138CBFB@xxxxxxxxxxxxxxxx
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?



.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: please share how you resolved
    ... Yep, it could happen, perhaps you need to re-write the query. ... I am exactly having same problem but this is from sql 2000 to ... On checking the Execution Plan, ... Clustered Index Seek on SQL 2000 whereas it is using Clustered Index Scan ...
    (microsoft.public.sqlserver.server)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Execute SQL UD Function call timeout, 1-3 seconds in query ana
    ... We did build a little ASP.NET page yesterday that lets us put a given sql ... query into a text box and then return the execution plan (using Showplan_Text ... Query Analyzer. ...
    (microsoft.public.dotnet.framework.adonet)