Modify Unicode with confidence?

  • Thread starter Thread starter Harold
  • Start date Start date
H

Harold

I am fearful of modifying the unicode property programtically with the
following snippe
Application.CurrentDb.TableDefs("Table1").Fields("Field1").Properties("UnicodeCompression").Value = True

I have some 30 tables that participate in relationships that I would like to
set uncode to yes to see if the performance can be imporved.

When I set the property manually I get messages during the save "Errors were
encountered during the save opoeration. "Data type were not changed." ..
"Indexes were not added or changed". If I manually delete the relationship
there is no issue with the save.

Because of the number of changes required the VBA snippet would work well
but I have no idea what is happening to the Indexes and relationships in the
darkness of night so to speak. If I do it manually, first deleting the
relationship and then restablishing the relationship I have a higher degree
of confidence but it is a lot of steps and work I could easily mess up.

Does anyone know what I can expect if I use the VBA snippet?
 
Personally, I'd think setting Unicode compression is akin to changing
the column data types and thus should be treated as such.

If you were to use SQL Server, MySQL, Oracle or whatever you would note
that whenever you create a text column, you have to either use the
default character sets (code pages in MS parlance)/collation or set one
yourself. Obviously, any indices upon those column has to use the same
sets & collations; imagine the weird results we'd get if we had unique
index with case sensitivity upon a case insensitive column!

In those servers, what you are doing would be akin to changing the
text's character set from ucs8 to utf8. Both are Unicode, but uc8 is
fixed width, consuming 2 byte per character regardless of the actual
character stored in the 2 byte whereas utf8 is 3 bytes variable length
character designed such way that all usual latin and numeric characters
would only consume 1 bytes while more exotic characters require 2 or 3
bytes. But that goes with the requirement that the indices has to be
rebuilt because you definitely can't compare a uc8 value against a utf8
value even if they both contain identical textual content. Hopefully, it
should become clear that the property "Unicode Compression" in Access is
a fairly oversimplification of what actually needs to be done to make
such transform feasible.

With this in mind, I'd definitely want to record all indices &
relationships upon those textual columns and delete them, change the
Unicode compression then rebuild the index & relationship. This then
guarantee that you have correct index for the now transformed column.


HTH.
 
Back
Top