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

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
zimmer9Asked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
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
 
harfangCommented:
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
 
zimmer9Author Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
harfangCommented:
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
 
harfangCommented:
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
 
Carl2002Commented:
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
 
Carl2002Commented:
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
 
rockiroadsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.