MS Access Too Many fields during UPDATE

2
votes

When I try to UPDATE a table in MS Access, it gives and error saying too many fields defined. I googled and saw that i need to Repair and Compact, which I did, but still does not work?

Anyone has any ideas? Also, what actually count towards the "too many field" counter?

Thanks.

2
ms-access
Can you provide some info about how you perform the update? Using SQL? - GolezTrol
Does the query include more than 255 fields? - Fionnuala
Thanks for the replies. The query was meant to run in a C# program I am writing. A foreach(hashtable) loop builds the query string. I stick a count++ in the loop and total no. of "fields=value" is 144. Copied the string to clipboard and paste in MS Access SQL design mode, the same error happens. Any idea? - Jake
Why not paste the string here? - Fionnuala
@Remou hmmm.. ok pasted it here. there are only 140 "=" symbols. On another note, an INSERT with the same number of fields does not generate this error. - Jake

2 Answers

5
votes

I seem to have found the answer:

The Microsoft Jet database engine has an internal limit of 255 fields per query. As the Microsoft Jet database engine iterates through the records in an update query, it creates a field for the original value and a field for the updated value. When more than 127 fields are selected, it reaches the 255 field limit of a query.

Consider the following SQL for an update query: UPDATE Table SET A=B, C=D Internally the query looks as follows: SELECT A,B,C,D FROM Table

-- http://support.microsoft.com/kb/199076

You have ~140 fields, do you not?

0
votes

Have you tried splitting the UPDATE query up across perhaps a few? Perhaps chunk it out to two or three - verifying each took before proceeding with the next? Could help toward your 'Too many fields' problem, and allow you to debug problems easier? Just a thought, your mileage may vary.