WITH COMPRESSION
chrisdev
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!
Tom van Stiphout
<chri...@gmail.com> wrote:
Where did you find this syntax? Do you have a link?
AFAIK this is not supported in Access.
-Tom.
Microsoft Access MVP
Bob Barrows
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:
> On Mon, 22 Nov 2010 05:50:13 -0800 (PST), chrisdev
> <chri...@gmail.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
chrisdev
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...
Bob Barrows
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.
--
HTH,
Bob Barrows
Bob Barrows
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
chrisdev
DESIGN...
Bob Barrows
> Thanks Bob, I was using DAO. But in any case, it should work in QUERY
> DESIGN...
development team. They've had a couple version releases now in which to
rectify that shortcoming.
David-W-Fenton
news:ice043$tia$1...@news.eternal-september.org:
> 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/
David-W-Fenton
news:9baa8fa7-cb9c-4704...@v19g2000yqa.googlegroups.co
m:
> 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).
Bob Barrows
> "Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in
> news:ice043$tia$1...@news.eternal-september.org:
>
>> 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.
Ah! Good thought: it hadn't occurred to me.
You are correct: switching to SQL-92 mode does allow the query to run in the
query builder.
David-W-Fenton
news:icj3lm$jf5$1...@news.eternal-september.org:
I'm no fan of DDL from within Access, so I don't believe it's worth
it to turn on SQL 92 mode to be able to run these kinds of
statements. There are just too many downsides and gotchas to running
in SQL 92 mode, seems to me (it breaks a whole lot of basic things
in Access).
The only place where DDL is justified, in my opinion, is from
outside Access, in which case you'd likely be using ADO/OLEDB,
anyway.
But within Access, I just don't see the point at all.
Albert D. Kallal
news:icj3lm$jf5$1...@news.eternal-september.org...
Or, just use the built in ADO connection object, and from debug window type
in:
currentproject.Connection.Execute "you ddl sql here"
So, the DDL will not work from the query builder unless you as noted turn on
sql 92, but that going to make a huge mess of your existing application
since simple syntax differences such as % and * and an good number of other
issues will crop up. So, while for some turning on sql compatibility is an
option, it really not a good idea for an existing built applications.
You can in code if you wanting to execute those ddl commands can use the
built in currentproject connection, and I believe you can do so without even
setting a reference to the ADO object library for this to work.
While the introduction of ADO was an issue, it also opened doors for many VB
ADO developers to use access without having to switch and re-write their
code. I also don't think it would be a good idea if DAO adopted the sql ansi
standards due to existing applications anyway.
In other words, you can use that syntax because of ADO support, but DAO does
not support that syntax.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenos...@msn.com
Bob Barrows
Outside of testing, I agree with you. It's nice to be able to test one's
queries in the native environment before attempting to run them from
external sources.
Bob Barrows
> ?"Bob Barrows" wrote in message
> news:icj3lm$jf5$1...@news.eternal-september.org...
>
>> David-W-Fenton wrote:
>>> "Bob Barrows" <reb0...@NOyahoo.SPAMcom> wrote in
>>> news:ice043$tia$1...@news.eternal-september.org:
>>>
>>>> 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.
>
>> Ah! Good thought: it hadn't occurred to me.
>
>> You are correct: switching to SQL-92 mode does allow the query to
>> run in the query builder.
>
> Or, just use the built in ADO connection object, and from debug
> window type in:
>
> currentproject.Connection.Execute "you ddl sql here"
>
> I also don't think it would be a good idea if
> DAO adopted the sql ansi standards due to existing applications
> anyway.
I don't agree. Where no conflict with existing syntax exists, there should
be no problem implementing more standards.
And anyways, the WITH COMPRESSION option is not standard. It's a
jet-specific syntax that will not work in other rdbms
> In other words, you can use that syntax because of ADO support, but
> DAO does not support that syntax.
This is like the third time this has been stated in this thread. We've got
it! :-)
David-W-Fenton
I don't know that one gains a damned thing by testing them in the
native environment, since there are literally hundreds of things you
can do in Access that you can't via ODBC or ADO/OLEDB. If you're
working in the outside environment, seems to me you should be
testing in that environment, and testing in Access doesn't really
tell you anything at all (except whether or not the SQL works in
Access, which has only marginal utility in most cases).
David-W-Fenton
news:Q0eHo.17559$2u4....@newsfe03.iad:
> While the introduction of ADO was an issue, it also opened doors
> for many VB ADO developers to use access without having to switch
> and re-write their code.
Albert, I think that's exactly backwards. People who used Jet before
ADO were perfectly fine with DAO, and got the benefit of all the
Access users who could help them out with their problems. ADO
introduced a lot of foreign concepts that don't help Access users
much at all, and then also required all the developers accustomed to
using Jet via DAO to learn the new interface, for very little
benefit to anyone.
I would think the number of developers who knew real SQL 92 syntax
at the time Jet 4 was introduced (and ADO) who would have been
considering using Jet was incredibly small.
But, in fact, the things that have been implemented in Jet 4 that
are referred to as "SQL 92" are a very incomplete set of features of
the real SQL 92, and there are dozens (if not hundreds) of
incompatiblities between Jet/ACE SQL and the real SQL 92 standard,
some of them quite significant. The result is that those who come to
Jet/ACE expecting to be able to write SQL 92-compatible SQL are
constantly running up against problems where Jet/ACE doesn't support
the standard at all.
> I also don't think it would be a good idea if DAO adopted the sql
> ansi standards due to existing applications anyway.
DAO doesn't have SQL. Jet/ACE does. And DAO should support all the
SQL commands that Jet supports. That it doesn't at this late date is
a huge scandal, in my opinion.
> In other words, you can use that syntax because of ADO support,
> but DAO does not support that syntax.
It's not ADO that supports it, but Jet/ACE. But MS never took the
time to update DAO to support all of Jet's features. This is
criminal. It is confusing and difficult and makes it hard for anyone
to know what does and doesn't work.
And there are no good references anywhere on full Jet/ACE SQL,
unfortunately. There are lots of very incomplete ones, many
reflecting the state of Jet SQL before version 4, but nothing that
I've found (or any of the other people I know of who've searched
long and hard) that include all the extensions included in Jet 4,
and that are supported sporadically between ADO and DAO.
It's a ridiculous situation, and the Access team really ought to
clean it up.
Albert D. Kallal
news:Xns9E3BAA730A925f9...@74.209.136.91...
>And there are no good references anywhere on full Jet/ACE SQL,
>unfortunately. There are lots of very incomplete ones, many
>reflecting the state of Jet SQL before version 4, but nothing that
>I've found (or any of the other people I know of who've searched
>long and hard) that include all the extensions included in Jet 4,
>and that are supported sporadically between ADO and DAO.
>It's a ridiculous situation, and the Access team really ought to
>clean it up.
Actually, the help file and sql reference included is rather nice.
The help file does have a separate area for the sql reference.
Here is a cut + paste into word,m and then saved as a PDF for help on create
table.
http://cid-b18a57cb5f6af0fa.office.live.com/self.aspx/.Public/HelpFile/CreateTable.pdf
The above is my public skydrive.
Note the compression documentation, and note the code examples, and note the
"see also" links at the end of the help document.
It is a rather nice sql reference. The above pdf is a cut + paste from the
access 2010 LOCAL help file (off line mode).
David-W-Fenton
> ?"David-W-Fenton" wrote in message
> news:Xns9E3BAA730A925f9...@74.209.136.91...
>
>
>>And there are no good references anywhere on full Jet/ACE SQL,
>>unfortunately. There are lots of very incomplete ones, many
>>reflecting the state of Jet SQL before version 4, but nothing that
>>I've found (or any of the other people I know of who've searched
>>long and hard) that include all the extensions included in Jet 4,
>>and that are supported sporadically between ADO and DAO.
>
>>It's a ridiculous situation, and the Access team really ought to
>>clean it up.
>
> Actually, the help file and sql reference included is rather nice.
But it doesn't help for using Jet/ACE from outside Access. It
doesn't fully document all the DDL that was introduced in Jet 4 (and
is still only available via ADO or in SQL 92 mode within Access).
It's a complete mess.
> The help file does have a separate area for the sql reference.
But it's really not complete.
> Here is a cut + paste into word,m and then saved as a PDF for help
> on create table.
>
> http://cid-b18a57cb5f6af0fa.office.live.com/self.aspx/.Public/HelpF
> ile/CreateTable.pdf
>
> The above is my public skydrive.
>
> Note the compression documentation, and note the code examples,
> and note the "see also" links at the end of the help document.
>
> It is a rather nice sql reference. The above pdf is a cut + paste
> from the access 2010 LOCAL help file (off line mode).
I don't have the complete details, but I believe this is not
complete. @onedaywhen on StackOverflow.com is always complaining
about the lack of comprehensive documentation of Jet/ACE SQL. This
looks better than anything I've seen before (it has CREATE VIEW and
CREATE PROCEDURE both included, which the old DDL documentation
lacked), but I suspect it's lacking things (such as the ability to
create CONSTRAINTs that are based on other rows -- yes, it'as
actually been possible since the release of Jet 4).
Even if the A2010 Help file is actually complete, it doesn't do
anybody good who doesn't have A2010. This documentation should be
separate from Access itself, as it's documentation of the behavior
of the database engine, which is usable from outside Access.
Albert D. Kallal
>Even if the A2010 Help file is actually complete, it doesn't do
>anybody good who doesn't have A2010.
If you have access 2003, from the VBA ide, go to help, and check out the
Microsoft JET sql reference.
That reference seems rather complete to me. The text is the same as 2010 to
my knowledge and includes the compression remarks and also has the same "see
also" links.
There might be some things missing, but from what I seen, it a rather nice
and complete reference.
I am not sure why someone was complaining, but perhaps it was someone who
did not have ms access, or did not bother to check the help file.
Albert D. Kallal
>Even if the A2010 Help file is actually complete, it doesn't do
>anybody good who doesn't have A2010.
If you have access 2003, from the VBA ide, go to help, and check out the
Microsoft JET sql reference.
That reference seems rather complete to me. The text is the same as 2010 to
my knowledge and includes the compression remarks and also has the same "see
also" links.
There might be some things missing, but from what I seen, it a rather nice
and complete reference.
I am not sure why someone was complaining, but perhaps it was someone who
did not have ms access, or did not bother to check the help file.
--
David-W-Fenton
> I am not sure why someone was complaining, but perhaps it was
> someone who did not have ms access, or did not bother to check the
> help file.
Indeed, it was someone who prefers to use Jet/ACE without Access
involved. And there is precious little documentation on exactly what
works in that environment as opposed to within Access.
Obviously, I can't offer much help myself in that scenario, so it is
something that concerns me. If there were proper documentation, I
think there'd be less kvetching about Jet/ACE/Access's non-standard
SQL implementation, for one.
--