You are currently offline, waiting for your internet to reconnect

ACC: "Too Many Fields Defined" Error Message Saving Table

This article was previously published under Q128221
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
When you save a table after you add a new field or change the propertiesof an existing field, you receive the message "Too many fields defined,"followed by the message "Errors were encountered during save. Data typeswere not changed." You receive these messages even though you have 255 orfewer fields defined in the table.

NOTE: You also receive this message if you add or modify fields in a reportthat is based on a table that has too many fields.

NOTE: This error message generates the Microsoft Jet Database Engine 3.0Reserved Error number 3190.

This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout 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 MicrosoftAccess versions 1.x and 2.0. For more information about Access Basic,please refer to the "Introduction to Programming" manual in MicrosoftAccess version 1.x or the "Building Applications" manual in MicrosoftAccess version 2.0
CAUSE
The internal column count that Microsoft Access uses to track the number offields in the table has reached 255, even though you may have fewer than255 fields in the table. This can happen because Microsoft Access does notchange the internal column count when you delete a field. Microsoft Accessalso creates a new field (increasing the internal column count by 1) forevery field whose properties you modify.
RESOLUTION
To free the internal column count for deleted fields or for fields whoseproperties you modify, do one of the following:
  • In Microsoft Access 7.0 or 97, click Save As/Export on the File menu and save the table under a different name. Then, delete the original table and rename to new table to the original table name.

    WARNING: Clicking Save As on the File menu in version 1.x or 2.0 copies only the structure of a table, not the records. Do not delete the original table until you use an append query to populate the new table.
  • In Microsoft Access 1.x or 2.0, click Save As on the File menu and save the table under a different name. Populate the new table with data from the original table. Then, delete the original table and rename to new table to the original table name.

    You can also free the internal column count by compacting the database.
STATUS
This behavior is by design.
MORE INFORMATION
In Microsoft Access, you can define up to 255 fields in a table. If youcreate 255 fields and then delete 10, Microsoft Access does not releasethe fields from the internal column count. Also, for every field whoseproperties you modify, Microsoft Access creates a new field and does notrelease the original field from the internal column count.

Steps to Reproduce Behavior

NOTE: For Microsoft Access version 1.x, manually create a table with255 fields, all with a data type of Text. The code below works only forMicrosoft Access 2.0 and later.
  1. Create the following Visual Basic for Applications code to create a new table with 255 fields:
          ' ****************************************************************      ' 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 255 fields, each of which has a Text data      ' type and a size of one character.      ' ****************************************************************      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 254            Set fld = tbl.CreateField("Field" & CStr(i + 1))            fld.type = DB_TEXT            fld.size = 1            tbl.fields.Append fld         Next i         mydb.tabledefs.Append tbl      End Function						
  2. Type the following line in the Debug window (or Immediate window in version 2.0) to run the function and create the table:
    ? Fill_Table()
  3. View the table Field Test in Design view, and delete the last field so that there are only 254 fields defined in the table.
  4. Add the field again, and try to save the table.

    In Microsoft Access 97, you see the following error messages:
    Too many fields defined.

    Errors were encountered during the save operation. Fields were not added. Properties were not updated.
    In Microsoft Access 7.0, you see the following error messages:
    Too many fields defined

    Fields were not added. Properties were not updated.
    In Microsoft Access versions 1.x and 2.0, you see the following:
    Too many fields defined

    Errors were encountered during save. Data types were not changed.
REFERENCES
For more information about table specifications, search the Help Indexfor specifications, or ask the Microsoft Access 97 Office Assistant.
Properties

Article ID: 128221 - Last Review: 01/19/2007 04:55:54 - Revision: 2.3

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kberrmsg kbprb kbprogramming kbusage KB128221
Feedback