ACC2000: "Too Many Fields Defined" Error Message in Update Query

ID: Q199076

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

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 by using a recordset in Visual Basic for Applications.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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 that updates the value in the fields to the letter "T." Run the three functions from the Immediate window and observe the error message.

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 DAO.Database
   Dim tbl As DAO.TableDef
   Dim fld As DAO.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 DAO.Database
   Dim fld As DAO.Field
   Dim rs As DAO.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 DAO.Database
   Dim qdf As DAO.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

To run each function, type the following lines in the Immediate window. Press ENTER after each line.


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

On the File menu, click Close and Return to Microsoft Access.

Run the UpdateTest query.

REFERENCES

For more information about updating recordsets, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type Update method (DAO) in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

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

Q198504 ACC2000: "Too Many Fields Defined" Error Message Saving Table

Additional query words: prb

Keywords : kberrmsg kbdta QryUpdat
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb