PRB: VB Record Too Large When Add or Update Record > 2K

Last reviewed: June 21, 1995
Article ID: Q111304
The information in this article applies to:

- Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SYMPTOMS

The following error is the result when you Update or Add a Text field in a table and the total record size exceeds about 2000 bytes for all fields combined (not counting Memo fields):

   Record is too large.
   [Trappable Error number 3047.]

CAUSE

Records in a table in a Visual Basic or in a Microsoft Access database are limited to slightly under 2K, not counting Memo fields. The "Record is too large" error occurs when you enter data into such a record, not when you define the table structure.

RESOLUTION

Redefine the table by making some fields shorter or by removing unneeded fields.

You can also avoid this problem by using fields with the Memo type instead of the Text type. You can set a field's Type property to 12 to get a Memo type, instead of 10 to get a Text type. When a Memo field is greater than 250 bytes or whenever the 2K limit is reached on a record, Visual Basic automatically puts the Memo field on a separate page in the database file. If your Text fields contain related data, you could further improve space usage by concatenating the fields into one large Memo field.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start a new project in Visual Basic. Form1 is created by default.

  2. Add the following to the Form Load event code:

       Sub Form_Load ()
    
          ' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
          ' Create an empty database with the following design:
    
          Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
          Const numfields = 9  'Number of text fields to add to db, minus 1.
          Dim db As Database
          Dim MyDS As Dynaset
          Dim tdef As New TableDef
          Dim FieldInteger As New field
          Dim fieldname0 As New field
          Dim fieldname1 As New field
          Dim fieldname2 As New field
          Dim fieldname3 As New field
          Dim fieldname4 As New field
          Dim fieldname5 As New field
          Dim fieldname6 As New field
          Dim fieldname7 As New field
          Dim fieldname8 As New field
          Dim fieldname9 As New field
          Dim uniqindex As New Index
    
          form1.Show  ' Must Show form in Load event for Print to work.
          Kill "c:\tempx.MDB"
          Set db = CreateDatabase("c:\tempx.MDB", DB_LANG_GENERAL)
          tdef.Name = "Testtable"    ' Name of table to create.
    
          'Define the fields in the Testtable table:
          FieldInteger.Name = "fieldinteger"
          FieldInteger.Type = 4   'Long integer
          fieldname0.Name = "fieldname0"
          fieldname0.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname0.Size = 255  ' Maximum size of field.
          fieldname1.Name = "fieldname1"
          fieldname1.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname1.Size = 255  ' Maximum size of field.
          fieldname2.Name = "fieldname2"
          fieldname2.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname2.Size = 255  ' Maximum size of field.
          fieldname3.Name = "fieldname3"
          fieldname3.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname3.Size = 255  ' Maximum size of field.
          fieldname4.Name = "fieldname4"
          fieldname4.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname4.Size = 255  ' Maximum size of field.
          fieldname5.Name = "fieldname5"
          fieldname5.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname5.Size = 255  ' Maximum size of field.
          fieldname6.Name = "fieldname6"
          fieldname6.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname6.Size = 255  ' Maximum size of field.
          fieldname7.Name = "fieldname7"
          fieldname7.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname7.Size = 255  ' Maximum size of field.
          fieldname8.Name = "fieldname8"
          fieldname8.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname8.Size = 255  ' Maximum size of field.
          fieldname9.Name = "fieldname9"
          fieldname9.Type = 10   ' Type 10 = Text. Type 12 = Memo.
          fieldname9.Size = 255  ' Maximum size of field.
    
          'Add the fieldinteger and fieldnameN fields to the Fields collection:
          tdef.Fields.Append FieldInteger
          tdef.Fields.Append fieldname0
          tdef.Fields.Append fieldname1
          tdef.Fields.Append fieldname2
          tdef.Fields.Append fieldname3
          tdef.Fields.Append fieldname4
          tdef.Fields.Append fieldname5
          tdef.Fields.Append fieldname6
          tdef.Fields.Append fieldname7
          tdef.Fields.Append fieldname8
          tdef.Fields.Append fieldname9
    
          'Define fieldinteger_index, the unique primary-key index:
          uniqindex.Name = "fieldinteger_index"
          uniqindex.Fields = "fieldinteger"
          uniqindex.Unique = True
          uniqindex.Primary = True
    
          'Append the fieldinteger_index index to the Indexes collection:
          tdef.Indexes.Append uniqindex
    
          'Append the tdef table definition (TableDef object) to the TableDefs
          'collection:
          db.TableDefs.Append tdef
          db.Close
          ' The above code creates the empty database.
          ' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    
          Set db = OpenDatabase("c:\tempx.MDB")     ' Open the empty database.
          Set MyDS = db.CreateDynaset("Testtable")  ' Make dynaset from table.
    
          For i = 0 To 5  ' Add index field values for 5 new records:
             MyDS.AddNew
             MyDS!FieldInteger = i
             MyDS.Update
          Next
          MyDS.MoveFirst  ' Move to the first record.
    
          ' Add more than 2K of string data to the fields in the first record:
          For j = 0 To numfields
             MyDS.Edit  ' Opens current record for editing, into copy buffer.
             f$ = "fieldname" & j
             Debug.Print f$
             ' The maximum allowed record size is a little less than 2K.
             ' Fields fieldname0 through fieldname6 are each assigned 255 bytes
             ' with no problem. However, when assigning fieldname7, following
             ' field assignment fails at run time with Error 3047:
             '     "Record is too large"
             MyDS(f$) = String$(255, "x") 'Assign 255 bytes to each text field.
             MyDS.Update  ' Saves the copy buffer to the table.
          Next
          MyDS.Close
          db.Close
    
       End Sub
    
    

  3. Start the program, or press the F5 key. After a few seconds, the program gives Error 3047, "Record is too large." Choose End from the Run menu to clear the error.

To correct this behavior, redefine the database using fields of type Memo instead of type Text. In the program listed above, replace all the

   fieldnamex.Type = 10

statements with:

   fieldnamex.Type = 12

where x = 0 to 9.


Additional reference words: 3.00 limitation specification larger smaller
bigger
KBCategory: kbprg kbcode kbprb
KBSubcategory: APrgDataOther


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.