Creating Record Sizes That Are Larger Than Half a Page

You can force Microsoft Jet to lock individual records by creating record sizes that are larger than half a page — that is, larger than 1,024 bytes. This works because Microsoft Jet does not begin storing a new record on a partially filled page unless it can fit the entire record on the page. This strategy wastes a lot of disk space and increases network traffic. However, if you decide to use pessimistic locking and absolutely must have record locking, you may want to consider this technique. In order to use this technique, you need to be able to estimate the size of records.

You can use the following table, which gives the size of fields with various data types, to calculate the size of your fields. Then add the sizes of all the fields in a record to estimate the size of the record.

When you estimate the size of records, you also need to account for overhead, which includes the following:

• Seven bytes per record for record overhead
  
  
• One byte variable-length field overhead for each Text, Memo, Hyperlink, and Long Binary (OLE Object) field
  
  
• One additional byte for every 256 bytes of the total space occupied by all Text, Memo, Hyperlink, and Long Binary (OLE Object) fields
  
  
• One byte fixed-length field overhead for each Yes/No, byte, Integer, Long Integer, AutoNumber, Single, Double, Currency, and Date/Time field

Note These numbers are for estimating the record size rather than calculating the exact size.

The contents of the Memo, OLE Object, and Hyperlink fields are stored elsewhere in the .mdb file, so you need to count only the overhead for their address pointers, as described in the preceding list.

Text fields present the greatest problem for estimating record size because they are variable-length fields. Microsoft Jet uses one byte per actual stored character up to the maximum length specified by the FieldSize property. Zero-length strings ("") use 1 byte; Null values use 0 bytes.

The recommended way to avoid the variable length of Text fields is to use the SQL data definition language (DDL) CHAR data type, which is a fixed-length data type. With this method, you don’t have to pad the field with data as you do with the methods described in the following paragraphs. The CHAR data type, however, is not available through the Microsoft Access user interface and can only be used with SQL DDL commands such as ALTER TABLE and CREATE TABLE.

See Also For more information about creating tables using SQL DDL statements, see Chapter 3, “Data Definition and Integrity.”

A less ideal strategy is to add one or more dummy Text fields to the table with default values that are 255 characters long until the record size exceeds 1,024 bytes. For example, if you estimated the record size to be at least 130 bytes, you would calculate the needed dummy fields as follows:

• Bytes you need to pad = (1,025 – 130) = 895 bytes.
  
  
• Each whole dummy text field = (255 + 2 bytes overhead) = 257 bytes.
  
  
• You need three completely filled dummy fields (257 * 3 = 771) of 255 xs plus one partially filled dummy field (895 – 771 – 1 overhead byte) of 123 xs.

Do not place these dummy fields on your forms. Whenever a new record is created, Microsoft Access automatically creates a record with the four x-filled dummy fields, which forces it into record-locking mode.

Using either of these techniques may cause performance degradations because the size of the database will increase due to the fact that every record will occupy 2K of disk space.

Using the Microsoft Access Format property is not recommended to force padding when a record is being added because only Microsoft Access recognizes the Format property associated with the table. Other applications that use Microsoft Jet do not recognize the Format property and would not properly pad the record.