Unicode Compression Questions

[Jon]

Hello everyone,

I have two questions, both dealing with unicode compression, that have
been bugging the heck out of me for a while. First one is: is there an
option in Access 2K and/or XP that can be set so that Unicode compression is
enabled by default for all new fields that are of some kind of char data?
Second: In the JET SQL help file it claims that there is a "WITH
COMPRESSION" option that can be used in a CREATE TABLE DDL statement, that
would be cool except whenever I create a such a statement in an access
create table query I get an error. Thanks in advance for any good answers,
both of these issues have been driving me nuts for while.

Jon

 

[RSGinCA]

First one is: is there an

option in Access 2K and/or XP that can be set so that Unicode compression is
enabled by default for all new fields that are of some kind of char data?

It looks like it IS on by default. Text fields that I set up have Unicode
compression set up without me doing anything.

And here's a quote from the Access help files: "To offset this effect of
Unicode character representation and to ensure optimal performance, the default
value of the Unicode Compression property for a Text, Memo, or Hyperlink field
is Yes."

How about showing us your SQL statement and the text of the error statement.

Rick

 

[Jon]

That's interesting. I've tried this both at home and at work and on both
PC's it defaults to unicode compression set to off. Here's the JET SQL
statement that I'm trying:

CREATE TABLE [Compressed Table] ([Text Column] CHAR(255) NOT NULL WITH
COMPRESSION)

Here's the template for a CREATE TABLE for JET form the help file:

CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH
COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL]
[index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

It baffles me why this won't work. Thanks for looking at this.




Jon

 

[RSGinCA]

Note: SQL is definately not my area of expertise!

I added my table/field interactively/online, not with SQL.

I also noticed someplace along the way that compression is NOT the default when
using SQL to add a field.

I also found this, in an article entitled "Intermediate Microsoft Jet SQL for
Access 2000" in the MSDN library:

"Note The WITH COMPRESSION and WITH COMP keywords listed in the previous SQL
statements can be executed only through the Jet OLE DB provider and ADO. They
will result in an error message if used through the Access SQL View user
interface."

Rick

 

[Jon]

Very interesting, it looks like I do get unicode compression when using the
access interface to create the table but I don't when using SQL DDL or using
a make table (ie SLECT INTO) query. That pretty much explains it then. It's
unfortunate that the Make Tables don't trigger it as I frequently use them
to load large data sets from other platforms into Access and it would be
nice if it did this automatically. Guess I'll just have to work around this
by using a two step proccess until MS wises up and fixes this in Access
2009.


Jon