Docmd.OutputTo problem

[sabavno]

Hi,

I need to output the table (22000 records) to excel spreadsheet. When I use the following code

DoCmd.OutputTo acOutputTable, "tblAutoPassExport", acFormatXLS

I receive the error message saying that 'Too many records to output'.

Is there a way around this limitation?

I'll appreciate your help

THanks.

 

[KyleS]

Hi,
What version of Access and Excel are you using? This might not be an Access limitation. Excel 95 was limited to 16,000 rows... (I believe) Kyle

 

[lameid]

This was kind of obscure to find but check out

http://support.microsoft.com/default.aspx?scid=KB;en-us;q201589

Short version is Access defaults exporting to Excel 5.0 (for 'backward compatibility') which has a maximum record limit of 16354 (subtract one for headers... Kb didn't mention that but I played before I asked freeanswers.com the right answer). The workaround is to import from Access in Excel.

Personally, I'd put my money on a .CSV file (text comma seaparated values).

And I thought the MaxLocksPerFile Registry hack was a pain in the butt.

 

[WilliaD]

Why not use the docmd.transferspreadsheet function? It lets you choose the version of Excel to "export" to.

 

[lameid]

Dolt! You would have thought Transferspreadsheet would have been in the KB. Or wait it is an MS KB article <sigh>.

Anyway, good thinking WillaD.

 

[Zmey2]

See:

http://www.zmey.1977.ru/Access_To_Excel.htm