WITH COMPRESSION

#1

Hi,

I tried to execute the below command, but "WITH COMPRESSION " is not
accepted... What can be the problem?

CREATE TABLE D1_1 (D1_1_no INTEGER CONSTRAINT primarykey PRIMARY KEY,
D1_1_name CHAR(50) NOT NULL WITH COMPRESSION );

Thanks a lot in advance!

#2

On Mon, 22 Nov 2010 05:50:13 -0800 (PST), chrisdev
<chrisdev3 (AT) gmail (DOT) com> wrote:

Where did you find this syntax? Do you have a link?
AFAIK this is not supported in Access.

-Tom.
Microsoft Access MVP

Quote:

Hi,

I tried to execute the below command, but "WITH COMPRESSION " is not
accepted... What can be the problem?

CREATE TABLE D1_1 (D1_1_no INTEGER CONSTRAINT primarykey PRIMARY KEY,
D1_1_name CHAR(50) NOT NULL WITH COMPRESSION );

Thanks a lot in advance!

#3

Very strange, Tom. In Access online help, under Jet SQL Reference, the
Create Table topic contains this:

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

followed by this in the remarks:

The WITH COMPRESSION attribute can be used only with the CHARACTER and
MEMO (also known as TEXT) data types and their synonyms.

The WITH COMPRESSION attribute was added for CHARACTER columns because
of the change to the Unicode character representation format. Unicode
characters uniformly require two bytes for each character. For existing
Microsoft® Jet databases that contain predominately character data, this
could mean that the database file would nearly double in size when
converted to the Microsoft Jet version 4.0 format. However, Unicode
representation of many character sets, those formerly denoted as
Single-Byte Character Sets (SBCS) can easily be compressed to a single
byte. If you define a CHARACTER column with this attribute, data will
automatically be compressed as it is stored and uncompressed when
retrieved from the column.

I had never heard of that attribute either so it is fortunate I decided
to doublecheck the online help.

So chrisdev, I'm not sure what your problem is. The syntax does require
Jet 4.0 ... is your table being created with that version of Jet?

Tom van Stiphout wrote:

Quote:

On Mon, 22 Nov 2010 05:50:13 -0800 (PST), chrisdev
chrisdev3 (AT) gmail (DOT) com> wrote:

Where did you find this syntax? Do you have a link?
AFAIK this is not supported in Access.

Hi,

I tried to execute the below command, but "WITH COMPRESSION " is not
accepted... What can be the problem?

CREATE TABLE D1_1 (D1_1_no INTEGER CONSTRAINT primarykey PRIMARY KEY,
D1_1_name CHAR(50) NOT NULL WITH COMPRESSION );

Thanks a lot in advance!

--
HTH,
Bob Barrows

#4

yes, I found it in MSAccess help.

This property is supported from MS Access 2000 and is available in
table design.

I tried it in Access 2000 & 2007 and same error appears...

#5

Can you turn on Unicode Compression in table design mode?
I just tried in a test database and I'm getting a syntax error as well.
Very strange. Unicode Compression is turned on on text fields via design
mode so compression does seem to be supported in my test database. The
query parser is rejecting the option in DDL for some reason.

chrisdev wrote:

Quote:

yes, I found it in MSAccess help.

This property is supported from MS Access 2000 and is available in
table design.

I tried it in Access 2000 & 2007 and same error appears...

--
HTH,
Bob Barrows

#6

chrisdev wrote:

Quote:

yes, I found it in MSAccess help.

This property is supported from MS Access 2000 and is available in
table design.

I tried it in Access 2000 & 2007 and same error appears...

I just found this:
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.

Sure enough, I was able to use this code to run the query:
Sub withcompression()
Dim cn As ADODB.Connection
Set cn = CurrentProject.AccessConnection
cn.Execute "create table test3 (textcol char(10) with compression)", ,
129
Set cn = Nothing
End Sub
--
HTH,
Bob Barrows

#7

Thanks Bob, I was using DAO. But in any case, it should work in QUERY
DESIGN...

#8

chrisdev wrote:

Quote:

Thanks Bob, I was using DAO. But in any case, it should work in QUERY
DESIGN...
Cant't argue with that. You should put your opinion to the product

development team. They've had a couple version releases now in which to
rectify that shortcoming.

#9

"Bob Barrows" <reb01501 (AT) NOyahoo (DOT) SPAMcom> wrote in
news:ice043$tia$1 (AT) news (DOT) eternal-september.org:

Quote:

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.

Likely SQL 92 mode would work, too.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#10

chrisdev <chrisdev3 (AT) gmail (DOT) com> wrote in
news:9baa8fa7-cb9c-4704-b702-2c7be0c0cc0a (AT) v19g2000yqa (DOT) googlegroups.co
m:

Quote:

Thanks Bob, I was using DAO. But in any case, it should work in
QUERY DESIGN...

Welcome to the wonderful world we live in, in the aftermath of
Microsoft's idiotic "ADO Wars," where Jet and DAO were attacked
unnecessarily, and crippled by having new functionality put in ADO
but into Jet's native data interface layer. I would hope that the
Access team has it on the table to rectify this in upcoming versions
of DAO, so it would end forever the disparity between ADO
(non-native, dead data interface library) and DAO (live, vibrant,
in-development native data interface library).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/