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

[harinath88]

Q_3_Mothly_LC is a sql passed through query

[harinath88]

1.the query Q_3_Monthly_LC is working in back_end.it fetches exact records.the problem is while exporting the data into excel is not working

2.Actually the requirement is to generate the excel report

3.I didnt check the value for acSpreadsheetTy peExcel9. i will check and tell u

4.I used

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

.
it is working for less than 17000 records.So i tried to use the below code,

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

but this is not working at all.it didnt export a single data.
5.i am performing a button on click event.
6. Q_3_Mothly_LC is a sql passed through query

[zmbd]

Harinath88:
I have pm'd you with some information about posting requirements.


As for your latest post:

1.the query Q_3_Monthly_LC is working in back_end.it fetches

Great... and that doesn't matter in the excel export if even a single value is in violation of the the restraints for entry into an excel cell. This is what your error is telling me. (see my post #14)

2.Actually the requirement is to generate the excel report

Not what Killer42 was asking you to do! The task was to see if the issue is strictly with Excel or if the issue is with the records themselves. Please refer to Killer42's post.

4 (sniped) it is working for less than 17000 records.So i tried to use the below code

SO you keep changing the code... not helping to troubleshoot the problem. Please refer to Killer42's post and try to answer the question as asked.

5.i am performing a button on click event.

It doesn't matter where the code is being called from. Please do as Killer42 has asked, go to Tools|Options, Editor tab and check that Require Variable Declaration is turned on. Once done please to a debug/compile and fix any errors that occur. If you run across errors that you can not fix... post those in a new thread... remember, only ONE question per thread.

6. Q_3_Mothly_LC is a sql passed through query

As a pass-thru query talks directly thru the ODBC connection, by-passing access, it may be that the transferspreads heet method chokes on such a large batch transfer. With pass thru queries, you may only be seeing a batch of records a time, the next set being shown as you page thru the records. Try a direct link to the table in the backend and build a standard select query that matches your passthru. Once done, use the new query as the source for the transferspreads heet method.

[harinath88]

The value for acSpreadsheetTy peExcel9 is 8

[harinath88]

i tried the option hich you suggest but i couldnt get anything :(

[zmbd]

You tried what option?

[harinath88]

i tried this, Tools|Options, Editor tab and check that Require Variable Declaration is turned on,but i idnt get the result

[Killer42]

The problem with this option is that it applies to any new objects you create in the future, not to existing ones.

To get the checking in an existing module (or form), add the statement Option Explicit at the start. (All the "Require... " option does is insert this into every new module.)

This won't necessarily solve any problems, but if you have any typos in your variable names, parameters or whatever, it will highlight them. They're a surprisingly common cause of issues.