Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

ALTER COLUMN WITH COMPRESSION not working

Posted on 2008-06-12
6
Medium Priority
?
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 143

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 38

Accepted Solution

by:
Geert Gruwez earned 750 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

647 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