ACC: "Too many fields defined" Error Message in Update Query

Last reviewed: May 26, 1997
Article ID: Q154070
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you run an update query with more than 127 fields selected, you may receive the error message, "Too many fields defined." However, this same query will run correctly when you select 127 fields or fewer.

CAUSE

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

RESOLUTION

Break down the update query into multiple update queries with 127 or fewer fields per query.

-or-

Update the fields through Visual Basic for Applications using a recordset.

For more information about updating recordsets, search the Help Index for "Update Method."

MORE INFORMATION

Steps to Reproduce Behavior

The following steps create three functions. The first function creates a table with 128 fields. The second function adds one record to the table and sets the value of the fields in that record to the word "TEXT." The third function creates an update query which updates the value in the fields to the letter "T." Run the three functions from the Debug window (or the Immediate window in Microsoft Access 2.0) and observe the error message.

NOTE: For Microsoft Access version 1.x, manually create a table with 255 fields all with a data type of Text. The code below works only for Microsoft Access version 2.0 and later.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

  1. Create the following code to create a new table, to add a record to the table, and to create an update query:

          ' ****************************************************************
          ' Declarations section of the module
          ' ****************************************************************
    

          Option Compare Database
          Option Explicit
    

          ' ****************************************************************
          ' The Fill_Table() function creates a table in the current database
          ' named Field Test with 128 fields, each of which has a Text data
          ' type and a size of five characters.
          ' ****************************************************************
    

          Function Fill_Table ()
    
             Dim mydb As Database
             Dim tbl As TableDef
             Dim fld As Field
             Dim i As Integer
             Set mydb = CurrentDb()
             Set tbl = mydb.CreateTableDef("Field Test")
             For i = 0 To 127
               Set fld = tbl.CreateField("Field" & CStr(i + 1))
               fld.type = DB_TEXT
               fld.size = 5
               tbl.fields.Append fld
             Next i
             mydb.tabledefs.Append tbl
          End Function
    
          ' ****************************************************************
          ' The Fill_Data() function adds one record to the table with
          ' all fields equal to "Text."
          ' ****************************************************************
    
          Function Fill_Data()
             Dim mydb As DATABASE
             Dim fld As Field
             Dim rs As Recordset
             Dim i as Integer
             Set mydb = CurrentDb()
             Set rs = mydb.OpenRecordset("Field Test")
             rs.AddNew
             For i = 0 to rs.Fields.Count - 1
                rs.Fields(i).Value = "Text"
             Next i
             rs.UPDATE
             rs.Close
          End Function
    
          ' ****************************************************************
          ' The Build_SQL() function creates an update query in the current
          ' database named Update Test which will update the 128 fields in
          ' the Field Test table to the letter 'T.'
          ' ****************************************************************
    
          Function Build_SQL()
             Dim mydb As DATABASE
             Dim qdf As QueryDef
             Dim x As String
             Dim i As Integer
             x = "Update [Field Test] SET "
             For i = 0 To 127
                x = x + "[Field Test].Field" & CStr(i + 1) & " = 'T', "
             Next
             x = Left(x, Len(x) - 2)
             Set mydb = CurrentDb()
             Set qdf = mydb.CreateQueryDef("UpdateTest", x)
          End Function
    
    

  2. To run each function type, the following lines in the Debug window (or the Immediate window in version 2.0). Press ENTER after each line.

          ? Fill_Table()
          ? Fill_Data()
          ? Build_SQL()
    

  3. Close the Debug window.

  4. Run the Update Test query.

REFERENCES

For more information about the "Too many fields defined" error message, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q128221
   TITLE     : ACC: "Too Many Fields Defined" Error Message Saving Table
 

	
	


Keywords : kberrmsg kbusage QryUpdat
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 26, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.