Solved

ALTER COLUMN WITH COMPRESSION not working

Posted on 2008-06-12
6
525 Views
Last Modified: 2013-11-23
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.
0
Comment
Question by:reforest
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21774008
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...
0
 

Author Comment

by:reforest
ID: 21774030
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.
0
 

Author Comment

by:reforest
ID: 21774106
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)?
0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 250 total points
ID: 21776602
add a new column to the table with compression
copy the data in the old column to the new column
delete the old column
rename the new column to the old column

-> ye old workaround routine
0
 

Author Closing Comment

by:reforest
ID: 31466756
Thanks for the advice on the workaround. Too bad there's no easy way.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now