WITH COMPRESSION

[Tom Ellison]

Dear friends:

Using Access DDL, I have not been able to use WITH COMPRESSION on a column
definition. I don't find an example of this in the help, nor in some major
reference books. I'm starting to think it doesn't work.

Running Access 2002 SP3.

While on the topic, aren't there a number of table design options that
cannot be set using DDL?

Format
Input Mask
Caption
Default Value
Validation Rule
Validation Text
Allow Zero Length
IME Mode
IME Sentence Mode

Is this correct? We can't set these with DDL.

Thanks,
Tom Ellison

 

['69 Camaro]

Hi, Tom.

Using Access DDL, I have not been able to use WITH COMPRESSION on a column
definition.

There are several ways to do it.

1.) In the immediate window:

CurrentProject.Connection.Execute "CREATE TABLE tblMyTable " & _
"(ID COUNTER (1, 1) " & _
"CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"CompText Text (50) WITH COMP NOT NULL DEFAULT 'Squeeze it', " & _
"TextFld Text (50) NOT NULL, " & _
"CompMemo Memo WITH COMP);"

2.) With the ADODB library in VBA code:

Public Sub CreateTable()

On Error GoTo ErrHandler

Dim cmd As New ADODB.Command
Dim sSQL As String

cmd.ActiveConnection = CurrentProject.AccessConnection
sSQL = "CREATE TABLE tblMyTable " & _
"(ID COUNTER (1, 1) " & _
"CONSTRAINT PrimaryKey PRIMARY KEY, " & _
"CompText Text (50) WITH COMP NOT NULL DEFAULT 'Squeeze it', " & _
"TextFld Text (50) NOT NULL, " & _
"CompMemo Memo WITH COMP);"
cmd.CommandText = sSQL
cmd.Execute

CleanUp:

Set cmd = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in CreateTable( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

3.) Set the SQL Server Compatible Syntax (ANSI 92) database option, then
create a new query and paste the following in the SQL View pane:

CREATE TABLE tblMyTable
(ID COUNTER (1, 1)
CONSTRAINT PrimaryKey PRIMARY KEY,
CompText Text (50) WITH COMP NOT NULL DEFAULT 'Squeeze it',
TextFld Text (50) NOT NULL,
CompMemo Memo WITH COMPRESSION);

While on the topic, aren't there a number of table design options that
cannot be set using DDL?

The default value can be set with DDL, but the others on your list cannot.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Jamie Collins]

The default value can be set with DDL, but the others on your list cannot.

A 'Validation Rule' can be created in Jet 4.0 DDL using the CHECK
syntax e.g.

CREATE TABLE tblMyTable
(ID COUNTER (1, 1)
CONSTRAINT PrimaryKey PRIMARY KEY,
CompText Text (50) WITH COMP NOT NULL DEFAULT 'Squeeze',
CONSTRAINT CompText__not_zero_length CHECK (LEN(CompText) > 0),
CONSTRAINT CompText__alpha_only CHECK (CompText NOT LIKE '%[!A-Z]%'),

TextFld Text (50) NOT NULL,
CompMemo Memo WITH COMPRESSION);

The others in the list refer to one front end application (Access)
rather than the Jet database, to which DDL pertains. The exception is
'Validation Text' which Jet honours, by including in the error
description, yet cannot be set using Jet DDL.

My CompText__not_zero_length CHECK above is the database equivalent of
Allow Zero Length = False (True being the default).

Jamie.

--

 

[Jamie Collins]

CREATE TABLE tblMyTable
(ID COUNTER (1, 1)
CONSTRAINT PrimaryKey PRIMARY KEY,
CompText Text (50) WITH COMP NOT NULL DEFAULT 'Squeeze',
CONSTRAINT CompText__not_zero_length CHECK (LEN(CompText) > 0),
CONSTRAINT CompText__alpha_only CHECK (CompText NOT LIKE '%[!A-Z]%'),

TextFld Text (50) NOT NULL,
CompMemo Memo WITH COMPRESSION);

On reflection, my original CompText__alpha_only rule containing 'ANSI'
wildcard characters would cause older components (e.g. DAO) to fail,
therefore is a little unprofessional. Here is an alternative that isn't
as neat' but should at least its implementation neutral:

CREATE TABLE tblMyTable
(ID COUNTER (1, 1)
CONSTRAINT PrimaryKey PRIMARY KEY,
CompText Text (50) WITH COMP NOT NULL DEFAULT 'Squeeze',
CONSTRAINT CompText__not_zero_length CHECK (LEN(CompText) > 0),
CONSTRAINT CompText__alpha_only CHECK (CompText + STRING$(50 -
LEN(CompText), 'x') LIKE
'[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]'),

TextFld Text (50) NOT NULL,
CompMemo Memo WITH COMPRESSION)

Jamie.

--

 

['69 Camaro]

Hi, Jamie.

A 'Validation Rule' can be created in Jet 4.0 DDL using the CHECK
syntax

Thanks for that. However, while a check constraint has a similar effect as
a Validation Rule Property does for validating the data, it's not quite the
same thing. For proof, after you've created the table by using your query,
you'll notice that when you open the table in Design View, there is no text
in the Validation Rule Property for the CompText column. Also, when you
delete this column in Design View or this table in the Database Window,
you'll find that you can't because check constraints exist. This doesn't
happen when a Validation Rule Property is set on the table or the column.
In order to delete the column or table in the GUI, you'd first have to run a
query such as the following:

ALTER TABLE tblMyTable
DROP CONSTRAINT CompText__not_zero_length,
CONSTRAINT CompText__alpha_only;

My CompText__not_zero_length CHECK above is the database equivalent of
Allow Zero Length = False (True being the default).

This too has the same effect on validating the data, but this check
constraint doesn't alter the column's "Allow Zero Length" Property. Since
Tom is looping through the Properties Collections of tables and fields via
VBA code, it's the property values that he's interested in, not the check
constraints.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

The default value can be set with DDL, but the others on your list
cannot.

A 'Validation Rule' can be created in Jet 4.0 DDL using the CHECK
syntax e.g.

CREATE TABLE tblMyTable
(ID COUNTER (1, 1)
CONSTRAINT PrimaryKey PRIMARY KEY,
CompText Text (50) WITH COMP NOT NULL DEFAULT 'Squeeze',
CONSTRAINT CompText__not_zero_length CHECK (LEN(CompText) > 0),
CONSTRAINT CompText__alpha_only CHECK (CompText NOT LIKE '%[!A-Z]%'),

TextFld Text (50) NOT NULL,
CompMemo Memo WITH COMPRESSION);

The others in the list refer to one front end application (Access)
rather than the Jet database, to which DDL pertains. The exception is
'Validation Text' which Jet honours, by including in the error
description, yet cannot be set using Jet DDL.

My CompText__not_zero_length CHECK above is the database equivalent of
Allow Zero Length = False (True being the default).

Jamie.

 

[Jamie Collins]

'69 Camaro wrote:
Hi Gunny,

while a check constraint has a similar effect as
a Validation Rule Property does for validating the data, it's not quite the
same thing. For proof, after you've created the table by using your query,
you'll notice that when you open the table in Design View ...

Ah. I thought it was implicit in this discussion that Jet SQL DDL only
has an effect at the engine level. Aplogies for making assumptions. Jet
SQL DDL never sets properties in the local app, be that Access or any
other number of front end applications.

Just to be clear: I think that creating a Validation Rule at the column
level in Access sets some local properties in addition to creating a
CHECK constraint at the table level in the engine, whereas the Allow
Zero Length property is exclusive to the local app and does not create
a CHECK constraint (but I have no idea why it doesn't and is left to
the diligent developer to create one themselves).

Jamie.

--