Alter validation using DDL

I have a field called [Franking] that has had a validation rule applied via
the table-design interface.
The rule is "=0 And =1".
I now need to change this rule programatically (ie using DDL) to "=0 And
=4"
(I can't use the table-design interface because I need to change clients
databases via a script).
I suspect I need to use ALTER TABLE with (possibly) ADD CONSTRAINT. However
all examples I find require a DROP CONSTRAINT first - but what is the name of
the current constraint?
Is it possible to do this, and if so , how?
Any help much appreciated.
Alister

Hi, Alister.

I suspect I need to use ALTER TABLE with (possibly) ADD CONSTRAINT.

Not quite. Constraints and Validation Rules are not the same thing,
although when applied correctly they can usually achieve the same effect.
To add or change a Validation Rule for an existing column, try:

Public Sub setValidationRule()

On Error GoTo ErrHandler

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tbl = db.TableDefs("MyTable")
Set fld = tbl.Fields("Col1")
fld.ValidationRule = "Between 0 And 4"

CleanUp:

Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

Exit Sub

ErrHandler:

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

End Sub

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/ex...ributors2.html for contact
info.


"AlisterN" u30834@uwe wrote in message news:6c0cc8eaa8242@uwe...
I have a field called [Franking] that has had a validation rule applied via
the table-design interface.
The rule is "=0 And =1".
I now need to change this rule programatically (ie using DDL) to "=0 And
=4"
(I can't use the table-design interface because I need to change clients
databases via a script).
I suspect I need to use ALTER TABLE with (possibly) ADD CONSTRAINT.
However
all examples I find require a DROP CONSTRAINT first - but what is the name
of
the current constraint?
Is it possible to do this, and if so , how?
Any help much appreciated.
Alister

"'69 Camaro" wrote:

I suspect I need to use ALTER TABLE with (possibly) ADD CONSTRAINT.

Not quite. Constraints and Validation Rules are not the same thing,
although when applied correctly they can usually achieve the same effect.

FWIW Validation Rules are exposed as CHECK constraints in the
INFORMATION_SCHEMA. The row-level Validation Rule for the table is given the
name ValidationRule.

However, using the ALTER TABLE syntax e.g.

ALTER TABLE Enrolment DROP
CONSTRAINT ValidationRule;

generates the error, 'CHECK constraint 'ValidationRule' does not exist.'

A column-level Validation Rule is given the name
[column_name_here].ValidationRule and using the ALTER TABLE syntax e.g.

ALTER TABLE Classes DROP
CONSTRAINT [seating_capacity].ValidationRule;

generates the error, 'Syntax error in ALTER TABLE statement', caused by the
square bracket notation. So even if the Validation Rule did map to the ALTER
TABLE syntax, Access/Jet's own naming convention would prevent it from being
dropped!

My advice would be, if you need to DROP a validation rule using SQL DDL then
implement it via a CHECK constraint in the first place (and, unlike
Access/Jet, ensure you give it a name to facilitate the drop rather than
prevent it!) Note all CHECK constraints in Jet are table level, therefore the
column name(s) must appear e.g.

ALTER TABLE Classes ADD
CONSTRAINT seating_capacity__value_range
CHECK (seating_capacity BETWEEN 0 AND 1);

Jamie.

--

This works fine - thanks alot. I was hoping to use DDL (easier to administer
under my circumstances) but at least I know I can do this if required.
(Sorry for the late reply - I've been on holiday!)
Alister

'69 Camaro wrote:
Hi, Alister.

I suspect I need to use ALTER TABLE with (possibly) ADD CONSTRAINT.

Not quite. Constraints and Validation Rules are not the same thing,
although when applied correctly they can usually achieve the same effect.
To add or change a Validation Rule for an existing column, try:

Public Sub setValidationRule()

On Error GoTo ErrHandler

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tbl = db.TableDefs("MyTable")
Set fld = tbl.Fields("Col1")
fld.ValidationRule = "Between 0 And 4"

CleanUp:

Set fld = Nothing
Set tbl = Nothing
Set db = Nothing

Exit Sub

ErrHandler:

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

End Sub

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/ex...ributors2.html for contact
info.

I have a field called [Franking] that has had a validation rule applied via
the table-design interface.
[quoted text clipped - 11 lines]
Any help much appreciated.
Alister

--
Message posted via http://www.accessmonster.com