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

• Microsoft Access 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

When you save a table after you add a new field or change the properties of an existing field, you receive the message "Too many fields defined," followed by the message "Errors were encountered during save. Data types were not changed." You receive these messages even though you have 255 or fewer fields defined in the table.

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

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

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

CAUSE

The internal column count Microsoft Access uses to track the number of fields in the table has reached 255. Microsoft Access does not change the internal column count when you delete a field. Microsoft Access also creates a new field (increasing the internal column count by 1) for every field whose properties you modify.

RESOLUTION

To free the internal column count for deleted fields or for fields whose properties 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. Rename the new table
    after you delete the original table.

    In Microsoft Access 1.x or 2.0, click Save As on the File menu
    and save the table under a different name. Rename the new table
    after you delete the original table.

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 you create 255 fields and then delete 10, Microsoft Access does not release the fields from the internal column count. Also, for every field whose properties you modify, Microsoft Access creates a new field and does not release the original field from the internal column count.

Steps to Reproduce Behavior

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

for "specifications," or ask the Microsoft Access 97 Office Assistant.

Keywords          : kberrmsg kbusage
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : Info_Provided