Exporting large tables to Excel

[Jay]

Can anyone advise how I can easily transfer large tables (?36,000 rows, 15
columns) to Excel from my .adp.

If I try 'Export' or 'Analyse with Excel' I obviously get the 'output
limitations....etc.' error message.

I've tried a 'TransferSpreadsheet' macro, but for some reason can't get it
to recognise the View names?

Any help greatly appreciated.....Thanks, Jason

 

[RJ]

I tried this on Acc 2003 and it doesn’t look the macro entry for
TransferSpreadsheet allows a View or Stored Procedure as a parm for File
Name. So I think you have 2 options.

The first is to create a new table in the format you wish to export, execute
a stored procedure to create the data into the new table and the then do the
TransferSpreadsheet against the new table. This is by far the easiest route.

Second option is to dive into VBA and do it in code (see CopyFromRecordset)
but I warn you it is not for the faint of heart.

Good luck,
RJ

 

[Sylvain Lafontaine]

You can use a Stored Procedure with the TransferSpreasheet command if you
are using an EXEC statement; for example:

DoCmd.OutputTo acOutputStoredProcedure, "Exec dbo.MySP parm1, ...",
acFormatXLS, "c:\test.xls"

For views, you must build a Select statement:


DoCmd.OutputTo acOutputStoredProcedure, "Select * from MyView where Id=1",
acFormatXLS, "c:\test2.xls"

Notice that you must use acOutputStoredProcedure or acOutputServerView in
both cases and that acOutputQuery shouldn't work. Don't forget to put dates
and string values between single quotes.

 

[Jay]

Thanks for the help guys - I'm pretty confident I'll be able to manage it
from this.

Regards.....Jason