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

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.

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.

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 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
   					

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

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

3. On the File menu, click Close and Return to Microsoft Access.
4. Run the UpdateTest query.