Alter validation using DDL

[AlisterN]

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]

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

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

 

[AlisterN via AccessMonster.com]

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

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/expert_contributors2.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