p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: OutputTo Problem


Message #1 by "Jeff Silverstein" <JeffASilverstein@c...> on Wed, 19 Jun 2002 14:37:39
I have the need to directly export the results of a query to Excel and am 
getting an error message "There are too many rows to output based on the 
limitations specified by the output format, or by Microsoft Access". There 
are a lot of rows, 18,000. The other factor that is perhaps of note is 
that I'm using a pass-through query to Oracle. Since 18,000 rows is not 
too much for Excel to handle I'm wondering what is causing this and if 
there is any solution other than getting into the Excel object model 
directly.

I appreciate any ideas.
Message #2 by "Leo Scott" <leoscott@c...> on Tue, 18 Jun 2002 23:42:06 -0700
OutputTo uses Excel5 format which is limited to 16k rows.  Look up the
DoCmd.TransferSpreadsheet command.  You can specify Excel97-2000 format with
it which allows 65k rows.  If you need more than that you will have to break
it into seperate tabs in the workbook.

|-----Original Message-----
|From: Jeff Silverstein [mailto:JeffASilverstein@c...]
|Sent: Wednesday, June 19, 2002 2:38 PM
|To: Access
|Subject: [access] OutputTo Problem
|
|
|I have the need to directly export the results of a query to Excel and am
|getting an error message "There are too many rows to output based on the
|limitations specified by the output format, or by Microsoft Access". There
|are a lot of rows, 18,000. The other factor that is perhaps of note is
|that I'm using a pass-through query to Oracle. Since 18,000 rows is not
|too much for Excel to handle I'm wondering what is causing this and if
|there is any solution other than getting into the Excel object model
|directly.
|
|I appreciate any ideas.
|

Message #3 by "Jeff Silverstein" <JeffASilverstein@c...> on Wed, 19 Jun 2002 16:32:08
Thanks very much. TransferSpreadsheet was really my next choice. I just 
couldn't figure out why OutputTo was giving me a problem. Thanks for the 
explanation.
Message #4 by "Jeff Silverstein" <JeffASilverstein@c...> on Wed, 19 Jun 2002 17:09:26
As it turns out TransferSpreadsheet doesn't appear to like pass-through 
queries. Back to the drawing board.
> Thanks very much. TransferSpreadsheet was really my next choice. I just 
c> ouldn't figure out why OutputTo was giving me a problem. Thanks for the 
e> xplanation.
Message #5 by "Leo Scott" <leoscott@c...> on Wed, 19 Jun 2002 10:12:04 -0700
Use your passthrough query to create a temporary table and use the temp
table to transferspreadsheet from.  Then just delete the temp table.

|-----Original Message-----
|From: Jeff Silverstein [mailto:JeffASilverstein@c...]
|Sent: Wednesday, June 19, 2002 5:09 PM
|To: Access
|Subject: [access] RE: OutputTo Problem
|
|
|As it turns out TransferSpreadsheet doesn't appear to like pass-through
|queries. Back to the drawing board.
|> Thanks very much. TransferSpreadsheet was really my next choice. I just
|c> ouldn't figure out why OutputTo was giving me a problem. Thanks for the
|e> xplanation.
|

Message #6 by "Jeff Silverstein" <JeffASilverstein@c...> on Wed, 19 Jun 2002 19:22:19
That, or use the pass-through as input to a standard select query, which 
Access doesn't complain about.
> Use your passthrough query to create a temporary table and use the temp
table to transferspreadsheet from.  Then just delete the temp table.

|-----Original Message-----
|From: Jeff Silverstein [mailto:JeffASilverstein@c...]
|Sent: Wednesday, June 19, 2002 5:09 PM
|To: Access
|Subject: [access] RE: OutputTo Problem
|
|
|As it turns out TransferSpreadsheet doesn't appear to like pass-through
|queries. Back to the drawing board.
|> Thanks very much. TransferSpreadsheet was really my next choice. I just
|c> ouldn't figure out why OutputTo was giving me a problem. Thanks for the
|e> xplanation.
|


  Return to Index