Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
1,052 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

877 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