Too Many Fields Defined

[billHassall]

I have a function that deletes existing data in tables, imports new data nad then exports queries into the same excel file. I have three different functions that are very similar, two of them run perfectly, the third comes back with an error message "too many fields defined". I have tried to compact and repair the database, I have tried to import everything out of the existing database into a new one, I have deleted and rebuild the query that is causing the problem and none of these have worked. The query itself if you just open it will run OK, it is only when I am trying to export the query that I have this problem. I have included the code that I am using, any help would be appreciated, I have highlighted in red the query that will not export

'------------------------------------------------------------
' Day 2 results
'
'------------------------------------------------------------
Function Day_2_Results()
On Error GoTo Monthly_Clears_Err

DoCmd.SetWarnings False

' Run Delete query Daily data
DoCmd.OpenQuery "Delete Daily Data", acViewNormal, acEdit
' Close query Delete Daily Data
DoCmd.Close acQuery, "Delete Daily Data"

' Run Delete PSPD Data
DoCmd.OpenQuery "Delete PSPD Data", acViewNormal, acEdit
' Close query Delete PSPD Data
DoCmd.Close acQuery, "Delete PSPD Data"



MyPath = CurrentProject.Path

DoCmd.SetWarnings False

' Import Daily Data.csv
DoCmd.TransferText acImportDelim, "Daily Data Import Specification", "Daily data", MyPath & "\Daily Data.csv", True, ""

' Import PSPD Data.csv
DoCmd.TransferText acImportDelim, "DCR Last Month Import Specification", "PSPD Data", MyPath & "\DCR Last Month.csv", True, ""


' Run all of the queries

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "11 Monthly Day 2 9R", MyPath & "\Day 2 Delivery.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "11 Monthly Day 2 5R", MyPath & "\Day 2 Delivery.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "0 PARBD L2C Estimates", MyPath & "\Day 2 Delivery.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "0 PARBD T2R Estimates", MyPath & "\Day 2 Delivery.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "0 PSPD Failure", MyPath & "\Day 2 Delivery.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "073 LLIB > 90 days Failures", MyPath & "\Day 2 Delivery.xls", True



Beep
MsgBox "Import Has finished, data exported to Day 2 Delivery.XLS", vbOKOnly, "Messag"


Monthly_Clears_Exit:
Exit Function
Monthly_Clears_Err:
MsgBox Error$
Resume Monthly_Clears_Exit
End Function

 

[vbaInet]

The query "11 Monthly Day 2 5R" most likely has more than the maximum number of columns allowed in an Excel spreadsheet, i.e. pre 2007.

Sometimes the error will still occur if the total number of fields present in the tables that form the query is greater than 255.

A workaround is to Make-Table from that query and export that query or revise your design.

 

[billHassall]

The part that confuses me is that the three similar functions that I have are a month to date which includes all nine of the measures that I need to report and this one runs perfectly. A day 6 report which has 6 of the 9 measures that I report and runs perfectly, but the day 2 report which only has three of the nine measures to report, comes back with the error code.

I am using Access 2003 which may not help

I have managed to find a work around but still don't understand why it didn't work, does the 255 refer to the total lines involved, the queries are all full of sub queries, so I assume it all adds up

Thanks for the reply

Bill

 

[vbaInet]

If it contains lots of sub queries it may add up.

It would be hard to tell you the exact problem without seeing your db.

What was the workaround you came up with?

 

[billHassall]

All that I have done is to use the 9 measure output and delete the six that I don't need, Day 2 is today so I needed to be able to run the figures this morning and didn't have time to spend on debugging. The database is something that started of as a simple database but has grown and grown without any check to ensure that it is designed in the best way, now is probably the time to re-visit

Bill

 

[vbaInet]

The database is something that started of as a simple database but has grown and grown without any check to ensure that it is designed in the best way, now is probably the time to re-visit

Bill

Turns out be the case most of the time and I'm sure you've learnt a great deal along the way.

I would second your re-visiting the db again if you haven't done so since its birth.

Happy developing!

 

[Babbage]

I had just the same problem with a certain query I was trying to export.

In my case, it was due to the name of a field in a query - it was supported in access as a field name but was obviously not allowed when exporting.

If memory serves me correctly (not with my DB at the moment), access was prepending a tablename to the field, and I was ending up with a field name of `blahblah-blahblah.originalname` - I altered this to make sure just `originalname` was returned.) I can't remember if it was the length of the fieldname, the fact access had managed to get a full stop in the fieldname, or something else that caused the problem. But it was something like that.

One way to test bad exports on a query is to make a copy of the query (thats failing) and then start removing columns and re-try exporting it till it works. Then explore what was wrong with the column (or part of the query) in question.

You can also try making a `second level` query that returns * from your first, and you can format that nice and neatly - doesnt matter what names are in the first - your second is specially formatted for export.

I actually had an `Object invalid or no longer set` error at first. The `too many fields` error started after I poked around with the query for a bit Exporting data to a file that already exists can also produce errors.

Bottom line - query results are not 100% compatible with how data is exported - but it's rare you ever come across it!

M.

 

[billHassall]

Thanks for that, I haven't had the problem since, but will bear i in mine if I do

Thanks

Bill