How to export 20000 records from a sql_pass through query to an excel file ?

[harinath88]

DoCmd.OutputTo acOutputQuery, "Q_3_Monthly_LC", acFormatXLS, txt, False,

this query is worked only for below 17000 recors but this fails while fetching more than 18000 records.

i tried to use this code but it is also not working

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Q_3_Monthly_LC", txt, False

[twinnyfo]

Are you receiving any type of error with your code? Please let us know and that will be a place to start.

[harinath88]

actually below is the error message we put in code.i got this message only. My excel file name is b_all.xls

MsgBox "Please, close b_all.xls file (Excel spreadsheet).", vbCritical, "File open"

[twinnyfo]

Try modifying your error handling code to include this:

MsgBox Err.Number & " " & Err.Description & ".  Please, close b_all.xls file (Excel spreadsheet).", vbCritical, "File open"

This should give you the error number and the type of error the code is encourntering when it stops.

[harinath88]

Hi,
i got this error message
3251 operation is not supported for this type of object.Please, close b_all.xls file (Excel spreadsheet).

[twinnyfo]

Hmmmmmmmmm..... Have you been able to generate the results of the query on its own, but just come across the error when you try to export? I know you said that 17,000 records worked fine. Even earlier versons of Excel should handle 65,000 records with no problem (and I've gone that far, too!).

[harinath88]

plese tell me the exact method to choose for fetching more than 20000 rows. i am using msexcel 2003.past five days i had been stuck into this code.could you please help me on this?

[twinnyfo]

The OutputTo method defaults to an earlier version of Excel which is limited to 16,000 records. The TransferSpreads heet method should work, but you need to have an Excel File out there for the query to save to.

DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "Q_3_Monthly_LC", "PathAndFileName.xls", True

[harinath88]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Q_3_Monthly_LC", txt, False

in the above code txt contains "C:\Sample\MyWo rk\b_all.xls"

[twinnyfo]

Try Excel type: acSpreadsheetTy peExcel9 and see if it gives you all the records. I don't believe acSpreadsheetTy peExcel3 will give you beyond 16,000 records.

[harinath88]

i tried acSpreadsheetTy peExcel9 ,but i am getting the same error.:(

[Killer42]

Quick question - can you output in CSV rather than Excel format? If so, this should avoid any restriction on the number of records Excel can handle (though it may well introduce other issues).

[Killer42]

Another thought. While looking for a longer-term solution, perhaps you can temporarily work around the problem by producing the output in chunks of up to, say, 15,000 records. You'd need to know your data to decide how to partition it.

[zmbd]

What we do not have is the actual format of the records being transfered... Records have two dimensions... rows and columns.
While excel-2003 and above have 65K+ rows the number of columns is only 256 columns.
We also don't know what is contained within the records. If there is a single field, in a single record that violates what excel will allow ( attachment fields, huge memo fields etc) http://office.microsoft.com/en-us/ex...005199291.aspx excel is going to choke.

Also, what is "Q_3_Monthly_LC " is this a query or a table?

[Killer42]

There are a couple of points I'd like to clarify, just to ensure we're clear on exactly what's going on. (Some of this has already been asked, but not answered)

1. Does the query run alright if you don't export it? How many records does it produce?
2. What happens if you export to something like a simple text or csv file? In other words, I'd like to make absolutely certain that the export to Excel is definitely the source of the problem.
3. Can you interrupt the code at that point, before it executes the DoCmd, examine the variable acSpreadsheetTy peExcel9 and tell us the actual value?
4. I find the specific error (3251) kind of suspicious; makes me wonder whether we're looking for the wrong problem. When you say the query works for a smaller number of records and fails for the larger amount, is it actually the same query? How does the number of records change?
5. If this code is in a Module, then go to Tools|Options, Editor tab and check that Require Variable Declaration is turned on. Not having this set can cause confusion because if you get a name wrong (for example acSpreadsheetTy peExce19) VB won't complain - it simply creates an empty local variable with that name. Note that setting this option affects all code modules (and I think problably forms) created in future, but not ones that already exist. That's why I want to see the value mentioned in point 3.
6. As zmbd asked, are you dealing with a query or a table?