Error: 3109 "Too Many fields defined" when using TransferSpreadsheet method

[rdougherty]

I am receiving this error when exporting a spreadsheet using the acTransferSpreadsheet method in VBA.

The help states that this is due to having more than 255 fields, but there are only 12! I have used this exact code to export a query with 24 fields, successfully, so I do not yet understand why this is happening to this new setup.

The query being exported is based on two linked queries, each with its own grouping. Does anyone know why this happens?

To be clear, the query works fine and runs just fine on its own. It is only when it is exported, that this error occurs, and ONLY after adding ANY 12th field.

[rdougherty]

I may have found the fix I need for today, but it will probably happen again. I found a quote from someone on another forum:

"i tried to repair the database... no luck there

so i deleted the spreadsheet and created a new one... this appears to have solved the problem..

Thanks"

I opened the spreadsheet that this is supposed to export to and replace each time, and it had zero data in it, and zero field names. Something must have failed to overwrite one time, and then after that, it didn't know how to match things up and overwrite again. Weird...

IF no one has other advise, please mark as "Solved"...

[ssanfu]

I use acTransferSpreadsheet a lot and never have any problems (office 2010).

What are you using for the "SpreadsheetType" parameter?
If Excel is 2010-2016, you should be using"acSpreadsheetTypeExcel12Xml" with the file name extension of ".xlsx".

FYI:
"acSpreadsheetTypeExcel12Xml" is for A2007
"acSpreadsheetTypeExcel9" is for A2000 and earlier - with extension of ".xls" .


You might want to set a bookmark to Ken Snell's site http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

[rdougherty]

Here is the line of code I am using. I have never been able to save this to any type but .xlsb which is chosen because it will not let me use .xlsx in the filename parameter...

EDIT: Ahh.. apparently the "xml" at the end of the acspreadsheettypeexcel12 parameter, allows for .xlsx. Thanks , I did not know that. That will be useful for possibly replacing data in an existing table I think.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry_BUILD_REPORT", "\\redactedservername\Programs\(..Scorecard\Cycle time\RAW DATA\" & "PASTE_DATA_INTO_REPORT", True, "YTD DATA"

[ssanfu]

Ack!!! I messed up.

In Post #3, it should have been
FYI:
"acSpreadsheetTypeExcel9" is for A2000 and earlier - with extension of ".xls" .
"acSpreadsheetTypeExcel12" is for A2007
"acSpreadsheetTypeExcel12Xml" is for A2010 - A2016