DoCmd.TransferSpreadsheet

[Bill H.]

I'm using the DoCmd.TransferSpreadsheet command to export to a spreadsheed
the results of a query.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"Qry_EmpHrs_Total", "Summary_Project_Hours", True


But I get an error msg, "Too many fields defined." The query is dymanically
build via vba.

The query only produces about 20 columns when I run it manually.

What's going on and how to fix?

Thanks.

 

[David Lloyd]

Bill:

Have you tried to Debug.Print the dynamically built query to check that it
is in the expected format and with the expected number of columns? Another
suggestion is to post the VBA code that builds the query dynamically, as it
appears the query is the most likely source of the issue.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm using the DoCmd.TransferSpreadsheet command to export to a spreadsheed
the results of a query.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"Qry_EmpHrs_Total", "Summary_Project_Hours", True


But I get an error msg, "Too many fields defined." The query is dymanically
build via vba.

The query only produces about 20 columns when I run it manually.

What's going on and how to fix?

Thanks.

 

[Bill H.]

The query appears fine.

It is the result of a crosstab query. The number of columns and rows
varies. I know it works because I have an option to print to a report using
the same query. Printing (using a report), and running the query in access
works fine, export doesn't when there's a "higher" column count. For
example, if I run the query using data that creates 7 columns and 12 rows,
that works. Change the parameters so that the query produces 20 columns and
10 rows, and the export fails.

This is what the crosstab looks like:

TRANSFORM Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS
SumOfSumOfEmpHrs
SELECT Qry_EmpHrs_Total_By_Project_Task.Task,
Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS TotTaskHrs
FROM Qry_EmpHrs_Total_By_Project_Task
WHERE (((Qry_EmpHrs_Total_By_Project_Task.Project_Label) = 'CSMES'))
GROUP BY Qry_EmpHrs_Total_By_Project_Task.Task
PIVOT Qry_EmpHrs_Total_By_Project_Task.Initials;

The exact error msg is:
Run-time error '3190:'
Too many fields defined.

Also, another question. I want to use a string variable for the filename in
the transferspreadsheet command, but I get an error when I try.

dim fName as string
fName= "long file name"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"Qry_EmpHrs_Total", fName, True

How can I get that to work?

 

[David Lloyd]

Bill:

I was able to export a crosstab with 69 columns and 397 rows without error.
I believe 255 columns is the maximum for an Access query. Whatever issue
you are having, it appears specific to your implementation.

Regarding the second question, there should be no reason why using a string
variable for the file name should cause an issue. Again, I tested this and
did not receive an error.

If you can give me more specifics regarding your code, I will try to help
you pinpoint the issue.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


The query appears fine.

It is the result of a crosstab query. The number of columns and rows
varies. I know it works because I have an option to print to a report using
the same query. Printing (using a report), and running the query in access
works fine, export doesn't when there's a "higher" column count. For
example, if I run the query using data that creates 7 columns and 12 rows,
that works. Change the parameters so that the query produces 20 columns and
10 rows, and the export fails.

This is what the crosstab looks like:

TRANSFORM Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS
SumOfSumOfEmpHrs
SELECT Qry_EmpHrs_Total_By_Project_Task.Task,
Sum(Qry_EmpHrs_Total_By_Project_Task.SumOfEmpHrs) AS TotTaskHrs
FROM Qry_EmpHrs_Total_By_Project_Task
WHERE (((Qry_EmpHrs_Total_By_Project_Task.Project_Label) = 'CSMES'))
GROUP BY Qry_EmpHrs_Total_By_Project_Task.Task
PIVOT Qry_EmpHrs_Total_By_Project_Task.Initials;

The exact error msg is:
Run-time error '3190:'
Too many fields defined.

Also, another question. I want to use a string variable for the filename in
the transferspreadsheet command, but I get an error when I try.

dim fName as string
fName= "long file name"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3,
"Qry_EmpHrs_Total", fName, True

How can I get that to work?

 

[John Nurick]

Bill,

1) I notice you're exporting to the antique Excel 3 format, which dates
back to the days of Windows 3.0 and has limitations not present in the
current Excel 8/9/10 format. Is this deliberate, to accommodate the
requirements of some other antique software you're using? If not, change
acSpreadsheetTypeExcel3 to acSpreadsheetTypeExcel8.

2) Are you exporting to a worksheet that already exists and has data on
it? If so, you may hit this or a similar error if there are more fields
in your query than in the existing data on the worksheet.

 

[Bill H.]

Well, that was interesting.

I changed to "8" and it exported a blank spreadsheet!

I'm using access 2000, if that matters.

If the spreadsheet already exists, it gets deleted by the export command (so
it seems)

 

[Bill H.]

Ah, that seems to help, once I found just where it put the @&$* file!

 

[AlienzDDS]

I am having this exact problem. Since I am already exporting to
acSpreadsheetTypeExcel9 I can't see what Bill did to solve the problem.

I am saving over the same spreadsheet each time since there is excel
formatting that the user requires (e.g. mmm-yy date) and the resulting
spreadsheet is only used to export to a third party software.

If I could figure out the code to delete the spreadsheet before exporting
again that might be a solution but I don't know how to do that.

Can anyone help?