export crosstab query too many fields defined

[ktdid]

Hello all -
I have a table with 6 fields and 1100 records. I use a crosstab query to
view the data in the format I need. That works great. When I try to export
it to Excel, however, I get an error of too many fields defined. Using the
compact and repair utility does not help. I use TransferSpreadsheet in a
macro to move the data to Excel. The error also occurs if I try to export
from the query. Any help would be very much appreciated.

(I posted this under general questions, too - hope that's ok?)

 

[ktdid]

I should probably mention that the crosstab query has about 50 columns and
about 35 rows.

 

[Phil Smith]

I have had the same problem. I have asked for help here, and although
some of the kind and knowledgable people here have made suggestions, I
have never actually got a solution here.

However, what I have found out on my own might help you out, although it
was not of as much help with me.

I do not think the problem is just with Access. Access has a lot of
sucky issues, and I personally think someone should pay for some of them
with blood, but the problem is likely with your Excell spreadsheet.

As a test, delete the spreadsheet, then try the transfer. In my case,
this allowed the transferspreadsheet, (or outputto, which also failed
for me the same way,) to operate. Of course, my whole reason for using
transfer spreadsheet is so that I could keep spreadsheet formatting, and
that idea went all to hell.

I am still looking for a solution that does not require me to delete my
spreadsheet, but if this is OK with you for your application:
Build a batch file by opening up notepad, typing:

del c:\my\path\and\filename.xls

and save it as c:\killfile.bat.

Then before your transfer spreadsheet command, put a runapp command to
run that batch file.

Please let me know what happens here, as I am really looking to spur a
better solution then that...

Phil

 

[text]

I have had the same problem.  I have asked for help here, and although
some of the kind and knowledgable people here have made suggestions, I
have never actually got a solution here.

However, what I have found out on my own might help you out, although it
was not of as much help with me.

I do not think the problem is just with Access.  Access has a lot of
sucky issues, and I personally think someone should pay for some of them
with blood, but the problem is likely with your Excell spreadsheet.

As a test, delete the spreadsheet, then try the transfer.  In my case,
this allowed the transferspreadsheet, (or outputto, which also failed
for me the same way,) to operate.  Of course, my whole reason for using
transfer spreadsheet is so that I could keep spreadsheet formatting, and
that idea went all to hell.

I am still looking for a solution that does not require me to delete my
spreadsheet, but if this is OK with you for your application:
Build a batch file by opening up notepad, typing:

del c:\my\path\and\filename.xls

and save it as c:\killfile.bat.

Then before your transfer spreadsheet command, put a runapp command to
run that batch file.

Please let me know what happens here, as I am really looking to spur a
better solution then that...

Phil





- Show quoted text -

It's is Excel related. After the first transfer, the range of columns
is define, and the name of the range is set. It's usually the name of
the Access query. You have to change the range to be equal to the
number of columns in your cross-tab query. The number of columns in a
cross-tab queries can change depending on the new data.

To change/define ranges for names, do this...

Click Insert\Name\Define.

hth

 

[ssjelite23]

It's is Excel related. After the first transfer, the range of columns
is define, and the name of the range is set. It's usually the name of
the Access query. You have to change the range to be equal to the
number of columns in your cross-tab query. The number of columns in a
cross-tab queries can change depending on the new data.

To change/define ranges for names, do this...

Click Insert\Name\Define.

hth

Thank you. I followed this procedure exactly and the same issue I was having was resolved. I just had to add in the new column to the definition and the access transfer worked perfectly.