Record Limit on export

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ct - 09 Dec 2006 00:28 GMT

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?

Reply to this Message

Daniel - 09 Dec 2006 03:22 GMT

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

http://groups.google.ca/group/comp.databases.ms-access/browse_thread/thread/c7aa
380a883e1464/cc949fa446641c95?

Daniel

> 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?

Reply to this Message

ct - 10 Dec 2006 03:59 GMT

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.

> I found this posting, I'm hoping it might help.
>
[quoted text clipped - 6 lines]
> > many rows I can export to Excel? I have read that this limit is 16,384 rows.
> > Is this limit different using SQL Server?

Reply to this Message

Ken Snell (MVP) - 10 Dec 2006 05:33 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

>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?

Reply to this Message

ct - 10 Dec 2006 15:13 GMT

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?

> 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
[quoted text clipped - 6 lines]
> > rows.
> > Is this limit different using SQL Server?

Reply to this Message

John Spencer - 10 Dec 2006 15:16 GMT

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.

> 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
[quoted text clipped - 11 lines]
> > rows.
> > Is this limit different using SQL Server?

Reply to this Message

ct - 10 Dec 2006 16:55 GMT

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!!

> 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
[quoted text clipped - 20 lines]
> > > rows.
> > > Is this limit different using SQL Server?

Reply to this Message

Ken Snell (MVP) - 10 Dec 2006 20:27 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> IF I recall correctly Access 2000 and later can have a limit of 10,000
> records
[quoted text clipped - 28 lines]
>> > rows.
>> > Is this limit different using SQL Server?

Reply to this Message