Outputting an Access DB query into Excel spreadsheet

[dbuuch]

The following VBA code is used to output the results of a query from an access db into Excel:

DoCmd.OutputTo acOutputQuery, sQueryName, "MicrosoftExcel(*.xls)", strPath

sQueryName, when run, has some 17,000 lines of records in it and this number will continue to grow but at a slow pace. In the past when the number of records was less (15k or so) this vba code used to work for me without any problem. however, for some time now, my VBA has been failing at this particular point and with the following Run-time error message:

Run-time error '2305': "There are too many rows to output, based on the limitation specified by the output format or by Microsoft Acess."

i know Excel will take up to 65k rows. but is there any such restrictions in Access and is my 17k rows of record the problem.

Is there anything i can do to fix this?

Thanks,
Daniel

 

[KenReay]

Which version of Access, and Excel?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[DaOtH]

Excel database queries might be an option,

In excel go to Data - Get externaldata - new database query

Offcourse when you want to output your data to excel and keep it there that won't work, but in fact you would be storing your data twice anyway which anyway would be good to avoid.

Keep it in your database, get it out using excel database queries if you want, when you want it.



"In three words I can sum up everything I've learned about life: it goes on."
- Robert Frost 1874-1963

 

[dbuuch]

i am now using MS Excel 2002 and Access 2002 SP3.

You've gotten me thinking - My pc was upgraded to XP and MSOffice 2002 some time last year. Also this code was written by a colleague some 2 or 3 years back with an earlier version of ms office.

if this is the problem, is there anyway to fix it?

Thanks four yor quick responses.

Daniel

 

[PHV]

For some historical reason, the OutputTo excel is limited at 16k (16384) rows.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Have you tried to play with the DoCmd.TransferSpreadsheet method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dbuuch]

As always, thank you all for your excellent help. I will play with the DoCmd.TransferSpreadsheet later but in the mean time I used Da0tH's suggestion, using "Get External Data" option from excel and it works perfectly well too. I have even managed to save the query and it works from any Excel Spreadsheet.

Thanks, again y'all.

Daniel