By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,824 Members | 2,359 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,824 IT Pros & Developers. It's quick & easy.

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

P: 13
Expand|Select|Wrap|Line Numbers
  1. 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


Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Q_3_Monthly_LC", txt, False
Sep 17 '12 #1
Share this Question
Share on Google+
22 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,283
Are you receiving any type of error with your code? Please let us know and that will be a place to start.
Sep 17 '12 #2

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

Expand|Select|Wrap|Line Numbers
  1. MsgBox "Please, close b_all.xls file (Excel spreadsheet).", vbCritical, "File open"
Sep 17 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,283
Try modifying your error handling code to include this:

Expand|Select|Wrap|Line Numbers
  1. MsgBox Err.Number & " " & Err.Description & ".  Please, close b_all.xls file (Excel spreadsheet).", vbCritical, "File open" 
  2.  
This should give you the error number and the type of error the code is encourntering when it stops.
Sep 17 '12 #4

P: 13
Hi,
i got this error message
3251 operation is not supported for this type of object.Please, close b_all.xls file (Excel spreadsheet).
Sep 17 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,283
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!).
Sep 17 '12 #6

P: 13
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?
Sep 17 '12 #7

twinnyfo
Expert Mod 2.5K+
P: 3,283
The OutputTo method defaults to an earlier version of Excel which is limited to 16,000 records. The TransferSpreadsheet method should work, but you need to have an Excel File out there for the query to save to.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "Q_3_Monthly_LC", "PathAndFileName.xls", True
  2.  
Sep 17 '12 #8

P: 13
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "Q_3_Monthly_LC", txt, False 
in the above code txt contains "C:\Sample\MyWork\b_all.xls"
Sep 17 '12 #9

twinnyfo
Expert Mod 2.5K+
P: 3,283
Try Excel type: acSpreadsheetTypeExcel9 and see if it gives you all the records. I don't believe acSpreadsheetTypeExcel3 will give you beyond 16,000 records.
Sep 17 '12 #10

P: 13
i tried acSpreadsheetTypeExcel9 ,but i am getting the same error.:(
Sep 18 '12 #11

Expert 5K+
P: 8,434
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).
Sep 18 '12 #12

Expert 5K+
P: 8,434
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.
Sep 18 '12 #13

zmbd
Expert Mod 5K+
P: 5,397
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?
Sep 18 '12 #14

Expert 5K+
P: 8,434
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 acSpreadsheetTypeExcel9 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 acSpreadsheetTypeExce19) 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?
Sep 19 '12 #15

P: 13
Q_3_Mothly_LC is a sql passed through query
Sep 22 '12 #16

P: 13
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 acSpreadsheetTypeExcel9. i will check and tell u

4.I used
Expand|Select|Wrap|Line Numbers
  1. 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,
Expand|Select|Wrap|Line Numbers
  1. 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
Sep 22 '12 #17

zmbd
Expert Mod 5K+
P: 5,397
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 transferspreadsheet 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 transferspreadsheet method.
Sep 22 '12 #18

P: 13
The value for acSpreadsheetTypeExcel9 is 8
Sep 25 '12 #19

P: 13
i tried the option hich you suggest but i couldnt get anything :(
Sep 25 '12 #20

zmbd
Expert Mod 5K+
P: 5,397
You tried what option?
Sep 25 '12 #21

P: 13
i tried this, Tools|Options, Editor tab and check that Require Variable Declaration is turned on,but i idnt get the result
Sep 26 '12 #22

Expert 5K+
P: 8,434
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.
Sep 26 '12 #23

Post your reply

Sign in to post your reply or Sign up for a free account.