Solved

ALTER COLUMN WITH COMPRESSION not working

Posted on 2008-06-12
6
526 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 37

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

24 Experts available now in Live!

Get 1:1 Help Now