How to Create Database with Memo Fields Up to 32000 Bytes

PSS ID Number: Q111314
Article last modified on 04-25-1994

3.00
WINDOWS

---------------------------------------------------------------------
The information in this article applies to:

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

SUMMARY
=======
When you use a database Update or Add method at run time, the sum of all
Memo field sizes in a record cannot exceed about 32000 or 33000 characters.
In the example program listed in the More Information section below, 33000
total characters write successfully to ten Memo fields. But writing 34000
characters causes the write to fail on the last Memo field. No error
message is displayed.
For fields of type Text, the sum of the Text field sizes in a record cannot
exceed about 2000 characters. You can use Memo fields instead of Text
fields to get a capacity of up to about 32000 or 33000 characters in a
record.

MORE INFORMATION
================
CAUTION: Large record sizes quickly consume a large amount of disk space as
you write new records. If you have large records, consider redesigning the
database to make use of related tables containing small record sizes.
The sample program below creates a new database (C:\TEMPZ.MDB) with 10 Memo
fields and one long-integer field. This program demonstrates the 32000-byte
or 33000-byte maximum size for the sum of all Memo fields.

Step-by-Step Example
--------------------
1. Start a new project in Visual Basic. Form1 is created by default.
2. Add the following code to the Form Load event:
   Sub Form_Load ()
      Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
      Const numfields = 9  'Number of Memo 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.
      On Error Resume Next  ' Ignore the error if file doesn't exist yet:
      Kill "C:\TEMPZ.MDB"   ' Delete db if it exists from previous run.
      On Error GoTo 0
      Set db = CreateDatabase("C:\TEMPZ.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 = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname1.Name = "fieldname1"
      fieldname1.Type = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname2.Name = "fieldname2"
      fieldname2.Type = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname3.Name = "fieldname3"
      fieldname3.Type = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname4.Name = "fieldname4"
      fieldname4.Type = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname5.Name = "fieldname5"
      fieldname5.Type = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname6.Name = "fieldname6"
      fieldname6.Type = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname7.Name = "fieldname7"
      fieldname7.Type = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname8.Name = "fieldname8"
      fieldname8.Type = 12   ' Type 10 = Text, Type 12 = Memo
      fieldname9.Name = "fieldname9"
      fieldname9.Type = 12   ' Type 10 = Text, Type 12 = Memo
      '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  ' Close and create the empty database.
      Set db = OpenDatabase("c:\TEMPZ.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 32000 total bytes of string data to the fields in first record:
      For j = 0 To numfields
         MyDS.Edit  ' Opens current record for editing, into copy buffer.
         f$ = "fieldname" & j
         Debug.Print f$
         ' The total size of all Memo fields added together cannot exceed
         ' about 33000 bytes.
         ' Fields fieldname0 to fieldname9 are each assigned 3200 bytes:
         MyDS(f$) = String$(3200, "x")
         ' If you increase the string size to 3300, all fields write okay.
         ' But if you increase the string size to 3400, the program fails
         ' to write a string in the last field, the fieldname9 field. That
         ' demonstrates the maximum allowed size.
         MyDS.Update  ' Saves the copy buffer to the table.
      Next
      MyDS.Close
      db.Close
      MsgBox "done"
      End
   End Sub
3. Start the program, or press the F5 key. After a few seconds, the program
   displays a message box saying "done."
4. Examine the new database C:\TEMPZ.MDB using Data Manager or Microsoft
   Access. You can run the Data Manager program from the Window menu in
   Visual Basic, or by using the Windows File Manager to run DATAMGR.EXE
   from the Visual Basic directory. To confirm that the number of
   characters in fieldname9 is 3200, copy it to the clipboard and paste
   it into a text editor. Close the database when finished; this will avoid
   a file-sharing conflict.
5. Change the String$(3200, "x") function in the above program so that it
   assigns 3400 characters to each of the ten Memo fields. Run the program
   again. Examine the new database using Data Manager or Microsoft Access.
   The last field, fieldname9, fails to receive any characters because the
   maximum record size was reached before 34000 characters.

Additional reference words: 3.00 limitation specification larger smaller
                            bigger
KBCategory: APrg
KBSubcategory: APrgDataAcc
=============================================================================
Copyright Microsoft Corporation 1994.