Need to turn on Unicode Compression programmatically in ms access

What error?  Perhaps this is a setting that cannot be changed.  Can you change the setting if you do it directly to the table?

try this:

   fld.properties("UnicodeCompression") = True

Select allOpen in new window

btw, you can use "fld" instead of "TD.Fields("temp")" ... and it may work better too ~

if this gives an error, the property may need to be created first

here is some general code you can use to set a property:

' set or change a database (or object) property
'~~~~~~~~~~~~~~~~~~~~~ Set_Property
Function Set_Property( _
   psPropName As String _
   , Optional pValue As Variant _
   , Optional pDataType As Long = 0 _
   , Optional Obj As Object _
   , Optional bSkipMsg As Boolean = True _
   ) As Byte
's4p ... 130410, 160820, 170721
      
   ' PARAMETERS
   ' psPropName is the (database) property name to set
   ' optional:
   ' pValue is the value for the property
   ' pDataType is the Data Type: dbBoolean, dbLong, dbText, ...
   '   if not passed -- uses defaults
   ' bSkipMsg = True: don't give user feedback
   ' obj = database, field, tabledef, querydef,
   '   or other object with properties
   '   if obj is not specified, then CurrentDb is used
   '
   'EXAMPLE
   ' Call Set_Property("AppTitle", sAppTitle, dbText, db)
   '              where
   '              sAppTitle is defined -- or a literal value

   'set up Error Handler
   On Error GoTo Proc_Err
   
   Dim booRelease As Boolean
   booRelease = False
   
   If Obj Is Nothing Then
      Set Obj = CurrentDb
      booRelease = True
   End If
      
   'assume property is defined
   Obj.Properties(psPropName) = pValue
   
Proc_Done:
   On Error Resume Next
   If Not bSkipMsg Then
      MsgBox psPropName & " is " _
      & Obj.Properties(psPropName) _
      & " for " & Obj.Name, , "Done"
   End If

Proc_Exit:
   On Error Resume Next
   If booRelease = True Then
      Set Obj = Nothing
   End If
   Exit Function
  
Proc_Err:
   'property is not defined
   Obj.Properties.Append Obj.CreateProperty( _
      psPropName, pDataType, pValue)
   Resume Proc_Done
   Resume
End Function

Select allOpen in new window

to set UnicodeCompression for fld to True, call using this:

   call Set_Property("UnicodeCompression ", true, dbBoolean, fld)   

Select allOpen in new window

have an awesome day,
crystal

Good Morning.... and thanks for your help... I'm still working on this.

@Crystal and @PatHartman.... I tried all of the methods suggested and none worked.  So as suggested by PatHartman I decided to try it once again, manually... and this time that did not work either.  I got a message that said "Too many fields"... which seemed very strange... this was using table [0000]... I have 230 fields in the table and about 330 tables.

Recompiling did not fix the problem nor did a reboot.... however copying the table to another name then renaming and deleting the original did correct the problem.

When I ran the code again I got the same problem with table [0001] and fixed it the same way.
I also got the same problem with table [0005] but not [0003] or [0004].

The problem is a message that too many fields exist when I try to append the field I have created with the size and allow zero null properties.  I have given up on the unicodecompression for now.

Does anyone have any idea what is going on?  This started as a project to write a routine to set the allow zero length property of all fields in the table to yes, and to set the size of the field to be consistent in all tables.  In doing so, I hoped to turn on unicodecompression as a bonus of the routine, however, now I'm just struggling to get the routine to run to stop my allow zero null errors!

Does anyone have any idea why I would get a too many fields error even if there are only 230 fields in the table?

If you have 230 fields (columns) in a table then i am afraid you are asking for troubles...you have serious design issues and you consider redesign it....probably the module provided has hard time handling so many fields...try it on tables with reasonable amount of fields...no more than 20..

@John  I don't think its the number of tables, but instead how often I programmatically manipulate the table's fields.  I have not proved it with code but it appears that Access does not decrement its internal field counter when a field is deleted.  

So lets say I programmatically change the size of a field... lets say its called PartNumber.  The only way I know to change the size is to create a new field that is the proper size.. I call it temp... then I  append the contents of PartNumber to temp... delete the original PartNumber field... then rename temp to PartNumber.

Each table should be able to have 255 fields... I'm pretty sure if you have a table with only once field and then change its size or any other parameter 255 times you'll get a too many fields error...even though you only have one field in the entire table.

Likewise, if you have 60 fields, and you need to change only one property of all 60 fields, Access seems to "think" you have 60 more fields than you really have.

Deleting the table and recreating seems to fix the counter.. as does compact and repair... but when you have a lot of fields to change this approach is way to time consuming...

I realize 230 fields seems like a lot, however, I'm confident its not a poor design.  With that said, there are a number of fields which I have abandoned... however deleting these fields cause a log of work because programmatic appends and other queries no longer work... so I leave them be...

Now that I am compacting the database to avoid the too many fields error, I have started to experience a record to large ero which is the new obstacle.  The record is not too large, something is screwed up inside Access.

If anyone has any idea which making numerous changes to the properties of fields of an Access table can cause a "Record Too Long" error please chime into this conversation.

Can you give us a rough idea why you do this kind of work...

Thanks again Crystal... that seems like a great idea...I’ll give it a try.  I won’t be able to change the names of the tables.. too much code involved and other program structure changes would be necessary... btw.. numbers as field names do work fine.. you just have to Remeber when to put the name in brackets.. second nature to me now after 15 years.. gives the advantage of easily cycling through all tables with loops...

you're welcome ~ happy to help

you could always change the table names and make queries that start with numbers to map to till you get stuff converted ... and for new stuff, use the new names. Brackets don't always work when names start with a number

I see the advantage of numbers, but there could be a letter before them and you can still loop

gives the advantage of easily cycling through all tables with loops

Yet another indication of a design flaw.  Sounds like you have a bunch of spreadsheets that you are calling tables and it is making lots of extra work for you.  Normalization will be a big job but it will help you move forward with less of a design change burdon.

@PatHartman  I'm sure it sounds that way, but that's not the case.  The structure of the tables that I referenced should all be identical.  Each table describe 230 properties of the components we sell.  For example, package, operating voltage, operating temperature, maximum voltage, maximum operating temperature, minimum storage temperature, maximum storage temperature, and the list goes on and on until we have 230 properties, including quantity, condition, etc.

Each client is assigned a four digit lot code, for example 1000, 1001, 1002, etc.

For simplicity, and to avoid mistakes, the lot code is the table number.  We could include all lots in the same table, and simply use a field to define the lot, however, maintaining the data specific to a specific customer's inventory provides greater data safe and security, as well as redundancy... keep in mind that the inventory constantly changes and needs to be adjusted.

The probably that a bad query will cause two customers inventory to get mixed up because of a bad query is zero, because we combine these customers tables into a new combined table, which is used for all of our queries.

We have billions of components in inventory, and the system has worked flawlessly for 15+ years.  So its a rock solid system that works for us, and our clients love the fact that they can access their inventory file online, and we have no concerns that giving customer ABC access to a table will allow them to see inventory.. through a hack or programming error, that belongs to customer DEF.

The problem we are having is that MS Access is not consistent with how it determined the default for allow null values and unicode compression.  Depending how the field was created it seem to select different defaults.  Today, we know about this inconsistency so we prevent problems by copying a standard structure... but due to the fact that our system evolved.. we occasionally have to go back to previous tables to add new fields.

I'm trying to set all fields to unicode compression because we only deal with english text, and because we have 2 billion+ items and therefore file size is a concern.

If you have any better ideas how to handles something like this, I'd be happy to hear them. :-)

@John  I tried to answer your question is my response to PatHartman above.

@Crystal  Understood, however, we have never had situations where the numeric table names have caused a problem... although like I said before there was a learning curve as to when to use the brackets and when no to, especially in SQL statements but it all works our.

We also have more than 1GB of code. Changing the number of characters will cause lots of work... for example we use left(PartNo,4) in perhaps hundreds of queries, many of which are hard coded.  We also have code to validate a number as one of our part numbers by the length of the number, position of the dash, and the first four and last six characters being numeric.

You are obviously enamored with your application design but you have a gig of code because of your design.  It is not normalized and no one who knows anything about database design would agreethat it was but if you're happy with it,  then don't change it at this point.

I will leave you with two things to ponder.
1. If a client adds a new product that has an attribute that is different from all the others, what do you do?  Add a new column to the table and modify all the necessary forms, reports, queries, and code -- that is the hallmark of a spreadsheet.
2. Would a bank have a separate table for each customer's transactions?  Does Amazon have a separate table for each type of product it sells?  Surely books have different attributes than shoes and snowblowers.  How do you suppose they handle the problem without hundreds of tables?

PS - when you adhere to best practices naming standards, you don't have to encase names in square brackets.

I agree with your points, and if this were an application developed today I would do many things differently... however the code was first developed in 2002 when A2K was released and at that time many concerns we different.

If it works...it works, but you do have design issues...perhaps its time to start for a new design.