Solved

Is there a way using the DoCmd.OutputTo command to generate more than the standard 16,384 records from an Access report ?

Posted on 2006-06-21
8
990 Views
Last Modified: 2008-02-01
Is there a way using the DoCmd.OutputTo command as follows

DoCmd.OutputTo acOutputReport, "rptD", acFormatXLS, ExportedFile

such that only the first 16,384 rows are sent to the first Excel worksheet and
then the next 16,384 rows get sent to a second Excel worksheet,
and then the next 16,384 rows get sent to a 3rd Excel worksheet, etc.
up to the max of 65,535 rows.

The reason I ask is because DoCmd.OutputTo will only generate 16,384 rows from an Access report.

I could do something like the following but I create subtotals and totals so I don't want to extract just the detail records:

    DoCmd.OpenReport "rptD", acDesign
    Reports!rptD.Filter = "LastName='Garrison'"
    Reports!rptD.FilterOn = True
    DoCmd.Close acReport, "rptD", acSaveYes
0
Comment
Question by:zimmer9
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 16955990
Hello zimmer9

Why do you need to export a report instead of a simple query? It seems the only (tiny) advantage of exporting a query is the automatic generation of groups, but that would fail anyway.

If you really need it this way, I guess you will have to rewrite the reports record source (i.e. the SQL of the query on which the record is based), export to different workbooks (or different worksheets of the same workbook, although that is tricky as well), and then have Excel bring the records together on one sheet (again, not entirely trivial).

This seems awfully complex. So, why a report?

(°v°)
0
 

Author Comment

by:zimmer9
ID: 16956092
Great question:

Let's say I have a table tblD comprised of Office, Cust, Date and Value.

How would I then create the following subtotals and totals via a query in order by the Date, Office, Cust, Acct (descending) fields using a query ?

A sample of the report would be like the following:

Office        Cust          Date                      Value
574           123           LESS THAN 6         300.00
574           123           LESS THAN 6         100.00

SUBTOTAL 123           LESS THAN 6         400.00

574           222           LESS THAN 6         300.00
574           222           LESS THAN 6         200.00

SUBTOTAL 222           LESS THAN 6         500.00

574           555           BETW 6 &  12        445.00
574           555           BETW 6 &  12        400.00

SUBTOTAL 555           BETW 6 &  12        845.00  

574           888           BETW 6 &  12        500.00
574           888           BETW 6 &  12        300.00

SUBTOTAL 888           BETW 6 &  12        800.00

GRANDTOT                 ALL DATE RANG  2,545.00
0
 
LVL 58

Expert Comment

by:harfang
ID: 16956438
Well, you wouldn't. Excel does that very well, why bother doing it in a query? Simply export the data (already sorted, Access sort is faster than Excel sort) and then add the subtotals in Excel.

If you really need it as a query (but you again loose the advantage of data groups and auto formatting in Excel), you could create three queries: data, subtotals, and total, bring them together in a UNION query and sort the whole lot so that the records intermix properly.

This would be slow an not very useful in the end. Well, this *is* the way the report engine works, but it won't be as efficient in a query, because of the final global sort when indexes are no longer availalbe.

(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 16956451
I must say that I'm puzzled. Then I always am when I get questions with that many rows.

What possible use can you have for a report that has several times 16k records? And who will ever even scroll one time accross that number of records stored in Excel? Let alone read any of it. In real life, you would never actually read any table with more than 100 rows. A useful report would either be a high-level synthetic overview or analysis of the entire data, or one precise extract to answer a detail question.

Anyway, none of my business (^v^)
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 16957265
Well I guess you can use Excel automation and recordsets, though its probably be slow
don't know how much slower it will be, and its more coding, but you will achieve what u want

e.g.


    Dim rs As DAO.Recordset
    Dim objExcel As Excel.Application
    Dim sSql As String
    Dim iMaxRowsTotal As Long
    Dim iMaxRowsPerSheet As Long
    Dim iNoSheets As Integer
    Dim iNoNewSheets As Integer
    Dim i As Integer
   
   
    sSql = "SELECT * FROM mytable"
    Set rs = CurrentDb.OpenRecordset(sSql)
    If rs.EOF = True Then
        MsgBox "No recs"
    Else
        'Create an excel object and create a new workbook
        Set objExcel = CreateObject("Excel.Application")
        objExcel.Workbooks.Add
       
        'Calculate number of sheets
        iMaxRowsTotal = rs.recordCount
        If iMaxRowsTotal = 0 Then
            rs.MoveLast
            iMaxRowsTotal = rs.recordCount
            rs.MoveFirst
        End If
       
        iMaxRowsPerSheet = 2
        iNoSheets = Round((iMaxRowsTotal / iMaxRowsPerSheet) + 0.5, 0)
       
        'Create those sheets
        Debug.Print "Number of Sheets", iNoSheets
       
        'Note, there are three sheets automatically created, so to use them up, u can do this
        If iNoSheets > 3 Then objExcel.Workbooks(1).Sheets.Add , , iNoSheets - 3
       
        For i = 1 To iNoSheets
            'Copy from range A1 in sheet number i,
            objExcel.Worksheets(i).Range("A1").CopyFromRecordset rs, iMaxRowsPerSheet

            'Name the sheet
            objExcel.Worksheets(i).name = "List " & i
        Next i
       
        objExcel.Workbooks(1).SaveAs "c:\ee\t1.xls"
        objExcel.Workbooks(1).Close
        objExcel.Quit
       
        Set objExcel = Nothing
    End If
   
    rs.Close
    Set rs = Nothing
0
 
LVL 4

Expert Comment

by:Carl2002
ID: 16958518
If you are only outputting to excel for printing purposes why not just use the report in Access or ouput to Word. By the sounds of things you dont actually want to do anything in excel so why output it there ?

Carl.
0
 
LVL 4

Expert Comment

by:Carl2002
ID: 16958521
You could always output as a csv, that doesn't limit at 16k and will output the max excel will handle on one worksheet.

Just a thought.

Carl.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16960039
zimmer9, note this line

iMaxRowsPerSheet = 2


Ived used it for testing, u may want to change that to your value


Now there is one issue with this, I don't know the max number of sheets u can have
I doubt very much you will exceed it though
If so then that is one heavy duty query of yours that is returning data
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now