Access 2K export to Excel - too much data

[Gary Wright]

I have an Access 2K database split into front and back. Quite often the
users want to do some data analysis that I have not created a report for so
they want to export some subset of the data into and Excel spreadsheet.
Since the data often comes from many different tables, I have decided to
create a temporary Access table, put all the data into it then use the
Docmd.TransferSpreadsheet command to output the table to a spreadsheet.

In general it works quite well. Except (here is my question) that if the
user chooses more than 255 fields to export. The temp Access table can't
have more fields than that though it isn't an issue in the target Excel
spreadsheet. Any nice solutions to this limitation?

I am considering dynamically creating the temp tables based on how many
fields are being exported and then breaking up the data into more than 1
table and exporting each temp table. This doesn't create a 'nice'
spreadsheet for the user to look at though as they have to combine sheets if
they want everything in one worksheet. Would I be better off using
automation (OLE or whatever it is called these days) to open Excel and then
write to the spreadsheet directly that way? Seems like it will be a lot of
extra work but may end up doing a better job.

Thanks in advance for your advice.

Gary.

[Tom van Stiphout]

On Tue, 24 Feb 2004 12:52:29 +0800, "Gary Wright"
<gw*****@pd.jaring.my> wrote:

255 is a hard limit for Access. SQL Server and MSDE go far beyond
that.
In Access, two or more tables in a 1:1 relation is the only solution.
Still, a query can also not have more than 255 columns, so the same
limitation applies come time to report on all this data.

Perhaps a relational database would apply? Where the one worksheet is
transformed to a dozen or so tables?

-Tom.

I have an Access 2K database split into front and back. Quite often the
users want to do some data analysis that I have not created a report for so
they want to export some subset of the data into and Excel spreadsheet.
Since the data often comes from many different tables, I have decided to
create a temporary Access table, put all the data into it then use the
Docmd.TransferSpreadsheet command to output the table to a spreadsheet.

In general it works quite well. Except (here is my question) that if the
user chooses more than 255 fields to export. The temp Access table can't
have more fields than that though it isn't an issue in the target Excel
spreadsheet. Any nice solutions to this limitation?

I am considering dynamically creating the temp tables based on how many
fields are being exported and then breaking up the data into more than 1
table and exporting each temp table. This doesn't create a 'nice'
spreadsheet for the user to look at though as they have to combine sheets if
they want everything in one worksheet. Would I be better off using
automation (OLE or whatever it is called these days) to open Excel and then
write to the spreadsheet directly that way? Seems like it will be a lot of
extra work but may end up doing a better job.

Thanks in advance for your advice.

Gary.

[DFS]

"Gary Wright" <gw*****@pd.jaring.my> wrote in message
news:c1**********@news4.jaring.my...

I have an Access 2K database split into front and back. Quite often the
users want to do some data analysis that I have not created a report for so they want to export some subset of the data into and Excel spreadsheet.
Since the data often comes from many different tables, I have decided to
create a temporary Access table, put all the data into it then use the
Docmd.TransferSpreadsheet command to output the table to a spreadsheet.

In general it works quite well. Except (here is my question) that if the
user chooses more than 255 fields to export. The temp Access table can't
have more fields than that though it isn't an issue in the target Excel
spreadsheet. Any nice solutions to this limitation?

Gary,

Excel 2000 has 256 columns (A to IV).

Can't your users get by with 255 or fewer fields of data? Sorry to say so,
but it sounds like there may be a design problem if you need to export that
many data fields.
I am considering dynamically creating the temp tables based on how many
fields are being exported and then breaking up the data into more than 1
table and exporting each temp table. This doesn't create a 'nice'
spreadsheet for the user to look at though as they have to combine sheets if they want everything in one worksheet. Would I be better off using
automation (OLE or whatever it is called these days) to open Excel and then write to the spreadsheet directly that way? Seems like it will be a lot of extra work but may end up doing a better job.

Thanks in advance for your advice.

Gary.

[Peter Russell]

Gary Wright previously wrote:

The temp Access table can't
have more fields than that True
though it isn't an issue in the target Excel spreadsheet

Must be a rather special version of Excel then.
Excel has 256 columns max.

Peter Russell

[Gary Wright]

"Peter Russell" <ru***@127.0.0.1> wrote in message
news:me***********************@russellscott.btinte rnet.com...

though it isn't an issue in the target Excel spreadsheet

Must be a rather special version of Excel then.
Excel has 256 columns max.

Peter Russell

Well don't I feel dumb. For some reason I have been under the impression
that Excel can have 64k columns but sure enough only 256! Guess my Access
problem isn't an issue after all since I can't export more than 256 anyways.
guess I'll have to think of something else like 2 worksheets or something.

Thanks for all the assistance anyways!

Gary