ACC: "Too Many Fields Defined" Error Message in Update Query (154070)

   UPDATE Table SET A=B, C=D
				

   SELECT A,B,C,D
   FROM Table
				

MORE INFORMATION

Steps to Reproduce Behavior

These steps assume 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

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

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.