Record Limit on export

[ct]

I am using Docmd.OutputTo to export a stored procedure (SQL Server) to Excel.
Only 10,000 records are getting exported. Is there a record limit on how
many rows I can export to Excel? I have read that this limit is 16,384 rows.
Is this limit different using SQL Server?

 

[Daniel]

I found this posting, I'm hoping it might help.

http://groups.google.ca/group/comp....ead/thread/c7aa380a883e1464/cc949fa446641c95?

Daniel

 

[ct]

Thanks.. but, I am not on a network, just on my single PC. I am not getting
any errors, the export is just returning a maximum of 10,000 records. When I
run the report using the stored procedure everything works fine. However, if
the report contains over 10,000 records then only 10,000 records export using
docmd.outputto and the same stored procedure.

 

[Ken Snell \(MVP\)]

OutputTo uses EXCEL 95 format, which means a maximum of 16,384 records. Use
DoCmd.TransferSpreadsheet to export the data - you can use the current EXCEL
format (65,536 rows) for the export.

 

[ct]

I can't use docmd.TransferSpreadsheet because I am using a Stored Procedure
with parameters. I don't want to make a temporary table because I want to
re-use the stored procedures that I am using for the reports (there is about
28 of them). I would understand if I was getting 16,384 records outputted to
Excel. However, I am only getting a maximum of 10,000 records. Is this
because I am using SQL Server?

 

[John Spencer]

IF I recall correctly Access 2000 and later can have a limit of 10,000 records
returned by a query if you are using ADO. It is one of the properties of a
query (again if I recall correctly). If the limit is set to 0 then all rows are
returned instead of a max of 10,000 (or whatever value is set).

I am on a computer where I can't check that right now, so I am relying on memory
only. I do know that you can set a limit on the number of rows returned, I just
don't recall exactly where you change the setting.

 

[ct]

Thank You!! You got me going in the right direction. I had found the max
records on a form, but not on a query. In Access (I am using Access 2003),
if you look under tools -> options and then the Advanced Tab their is a
property under client server section "Default Max Records". That property is
defaulted to 10,000. When I changed this to 0 then then my Export worked as
expected. It now exports over 10,000 records and if the export has over
16,384 records I receive an error message "There are too many rows to
ouput....". I have been googling for over two days and it was soooo simple!!

 

[Ken Snell \(MVP\)]

Thanks, John. Your post triggered a past memory for me, and I see that ct
found the setting in Tools | Options that controls this.