DDL SQL to alter ValidationRule and ValidationText

[Sam Davis]

Hi and thanks for reading....

Is it possible to use an ALTER TABLE to change the ValidationRule and Text
for a field. I'm sure I've done this before but just can't locate the
syntax.

I have an UpgradeBE routine that alters tables/fields automatically in the
BE when I upgrade the FE. It essentially runs a series of SQL statements,
hence I'd like to do this using SQL rather than via DAO or otherwise.

Regards, Sam

 

[Michel Walsh]

Within the SQL parlance, it is a CONSTRAINT, so the syntax is:

ALTER TABLE tableName ADD CONSTRAINT constraintName constraint;

such as, for example:

ALTER TABLE table1 ADD CONSTRAINT uniqueQuantity UNIQUE(Quantity) ;



I don't think you can change the text of the error, using DDL, that text is
maintained by Access, not by Jet, if my memory don't fail me.



Jet 4.0 Extensions also allow to handle the CHECK constraints. But to
use Jet 4.0 Extensions, you need to use ADO,
CurrentProject.Connection.Execute, not DAO, CurrentDb.Execute. That CHECK
constraint may be use for individual columns as well as for row validation,
and inter-rows/inter-tables validations (other than DRI).




Vanderghast, Access MVP

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've found that ADO doesn't allow adding a CHECK constraint to a column,
which means you can only set a CHECK on the table, which, essentially,
comes down to the same thing 'cuz the CHECK's <search conditions> have
to include the column name.

ALTER TABLE Animals ADD CONSTRAINT ck_age CHECK ([age]>35)

If you want to use DAO you can change the Validation Rule (aka CHECK) on
a column (Field) like this:

CurrentDb.TableDefs("Animals").Fields("age").Properties("ValidationRule"
..Value = "[age]>35"

Be sure to enclose the column name in square brackets, otherwise DAO
will enclose them in double quotes!

Here's how to change the Validation Text of a column (Field).

CurrentDb.TableDefs("Animals").Fields("age").Properties("ValidationText"
..Value = "Age must be greater than 35"

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeZyfIechKqOuFEgEQKeBQCgjMl0SGjsN6tKI3NK75txLlvGkUEAoJDk
A5nHaRB2CpnDHG8qrKsv1KEt
=Jn/J
-----END PGP SIGNATURE-----

 

[Sam Davis]

Thanks for the comments...

In this case I really need to alter both ValidationRule and ValidationText -
seems it can't be done via SQL. I guess I'll have to extend my "UpgradeBE"
routine so I can do it via DAO code (as MGFoster suggests) - no doubt I'll
need it for future projects anyway.

The upgraded FE will be used to open old archived BEs, hence the need to
upgrade and link to the old BE in code..

Regards, Sam

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I've found that ADO doesn't allow adding a CHECK constraint to a column,
which means you can only set a CHECK on the table, which, essentially,
comes down to the same thing 'cuz the CHECK's <search conditions> have
to include the column name.

ALTER TABLE Animals ADD CONSTRAINT ck_age CHECK ([age]>35)

If you want to use DAO you can change the Validation Rule (aka CHECK) on
a column (Field) like this:

CurrentDb.TableDefs("Animals").Fields("age").Properties("ValidationRule"
.Value = "[age]>35"

Be sure to enclose the column name in square brackets, otherwise DAO
will enclose them in double quotes!

Here's how to change the Validation Text of a column (Field).

CurrentDb.TableDefs("Animals").Fields("age").Properties("ValidationText"
.Value = "Age must be greater than 35"

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSeZyfIechKqOuFEgEQKeBQCgjMl0SGjsN6tKI3NK75txLlvGkUEAoJDk
A5nHaRB2CpnDHG8qrKsv1KEt
=Jn/J
-----END PGP SIGNATURE-----

Within the SQL parlance, it is a CONSTRAINT, so the syntax is:

ALTER TABLE tableName ADD CONSTRAINT constraintName constraint;

such as, for example:

ALTER TABLE table1 ADD CONSTRAINT uniqueQuantity UNIQUE(Quantity) ;



I don't think you can change the text of the error, using DDL, that text
is maintained by Access, not by Jet, if my memory don't fail me.



Jet 4.0 Extensions also allow to handle the CHECK constraints. But to
use Jet 4.0 Extensions, you need to use ADO,
CurrentProject.Connection.Execute, not DAO, CurrentDb.Execute. That CHECK
constraint may be use for individual columns as well as for row
validation, and inter-rows/inter-tables validations (other than DRI).




Vanderghast, Access MVP