Microsoft KB Archive/111314

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:.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:.MDB” ’ Delete db if it exists from previous run. On Error GoTo 0 Set db = CreateDatabase(“C:.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:.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<math display="inline">) = String</math>(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:.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.