Link to home
Start Free TrialLog in
Avatar of reforest
reforest

asked on

ALTER COLUMN WITH COMPRESSION not working

Hi all experienced programmers,

I have created a table in code that has a text column, but I realized later that I wanted it to have Unicode Compression set to yes. I have users that are already running my program, and therefore they already have this column created in their table. I tried to alter the table (which contains data) for my next release using the following code:
  with MyQuery do
  begin
    Close;
    SQL.Clear;
    SQL.Add('ALTER TABLE MyTable ');
    SQL.Add('ALTER COLUMN Name CHAR(25) WITH COMPRESSION');
    ExecSQL;
  end;
However, the Unicode Compression for the Name column is still set to NO. What else should I try?

I also don't know how to set 'Allow Zero Length' to NO.

Your help is much appreciated.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

is this ms access?
I see that "WITH COMPRESSION" for the first time, and I don't remember having seen it for ms sql server...
Avatar of reforest
reforest

ASKER

Yes MS Access.
I have tried using 'WITH COMPRESSION' when I first create the table and it works. Unfortunately, I already have users with the table created already so I have to alter it.
Thanks. I checked a few more things and this is definitely working on an empty table but not on a table which contains data.

If this is just something that won't work, maybe someone can suggest a workaround (how to copy the data from my existing table to a new table with the correct properties then delete the old table and rename the new one)?
ASKER CERTIFIED SOLUTION
Avatar of Geert G
Geert G
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the advice on the workaround. Too bad there's no easy way.