Free Resources from FMS

Additional Resources

 

Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities.

Darren D.

 

Free Catalog

 

Beware of SQL Code that Creates Tables without Unicode Compression

Provided by: FMS Development Team

Note: This information applies only to a Microsoft Access databases

What is Unicode Compression?

Starting with Microsoft Access 2000 and continuing through the current version, all data for the Text data types (Text, Memo, or Hyperlink field) are stored in the Unicode 2-byte character format. Languages such as Chinese, Japanese, and Korean use the full 2-byte characters, but languages that use Latin characters (e.g. English, Spanish, and German) always use a 0 as the first byte. When a field's Unicode Compression property is set to Yes, any character whose first byte is 0 is compressed when it is stored and uncompressed when it is retrieved, thus saving space in the database.

By Default

By default, Unicode Compression is set to ‘Yes’ when you create the field via the table properties. It is when you create the field via SQL that you must explicitly state the Unicode Compression to be ‘Yes’. Otherwise, the setting is left untouched with a value of ‘No’. To see the property to ‘Yes’, use the WITH COMPRESSION (or WITH COMP) keywords at the field-level declaration.

IMPORTANT NOTES:

  1. The WITH COMPRESSION can be executed only through the Jet OLE DB provider and ADO.
  2. Creating the table via SQL (by DAO or ADO), the Unicode Compression is set to ‘No’ by default.

Setting the property

The Unicode Compression is a property setting of the field. After making the data type selection, the property is located on the General tab at the bottom of the table property page.

Total Access AnalyzerIs there a fast way to ensure I have the Unicode Compression turned on?

FMS's Total Access Analyzer performs detailed analysis of your Access database objects. The detection of the Unicode Compression property is just one of hundreds of checks that can be performed on your Microsoft Access application.

Example

The following example illustrates two fields, with and without Unicode Compression being set.

Public Sub CompressionPropertyTest()
  Dim strSQL As String

   strSQL = "Create Table MyFirstTable ( FirstName_CompressionOFF Text(15), " & _
            "LastName_CompressionON Text(15) WITH COMPRESSION)"

  CurrentProject.Connection.Execute strSQL

End Sub

Return to the tips page