Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2140
  • Last Modified:

Export an Access Report to Excel by passing overflow error (either can be done or it can't)

hi there,
I have an access report that its 7682 pages is there a way to export it to excel? i have tried before but get overflow error after 1209 pages. is there another way of doing this?
Thanks,
jsctechy
0
jsctechy
Asked:
jsctechy
5 Solutions
 
Rey Obrero (Capricorn1)Commented:
excel has a limit of 65000 + rows.
you can divide your reports record source into several queries and export the queries to different excel files.

is this acceptable?
0
 
Patrick MatthewsCommented:
Hello jsctechy,

If you are running Excel 2003 or earlier, you are limited to 65,536 rows in a worksheet, so if your
Access report needs almost 8000 pages, it does not look like good odds.

Regards,

Patrick
0
 
Patrick MatthewsCommented:
jsctechy,

BTW, Excel 2007 boosts to 1,048,576 rows.  Of course, I have no idea whether Access's
routine for the export can exploit this new capability or not...

Regards,

Patrick
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I have an access report that its 7682 pages is there a way to export it to excel?
Very bad idea.  Access Reports do not export to other editable formats very well, and often resemble a Picasso of their original cosmetic formatting.
Either export the query that serves as your report's RecordSource to Excel, or text if you go up against the maximum rows limit as the above experts stated, or write to a pdf or snapshot file.
0
 
jsctechyAuthor Commented:
all your suggestion sound good but the need to export to excel is inmiment. They need to calculate and add however they want so they need it in excel format.
i like the idea of divide the query in different queries and have multiple files i think thats the best thing i can do.
Thanks,
jsctechy
0
 
stevbeCommented:
jsctechy,
   Have you tried exporting the query the report is based on? How many records are displayed if you just run the query ... if less than 65,536 then that would be the course of action to persue. This will likely get them better results to work with (Access does a bad job of exporting reports into Excel) anyway, no umping columns, funny column headers etc. You can control the headers that will be put into Excel by aliasing the filed names in the query itself.
SELECT MyFiled As FieldOne

<Of course, I have no idea whether Access's routine for the export can exploit this new capability or not...>
nope ... 2007 can not export a report to Excel AT ALL.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now