Alter validation using DDL

  • Thread starter Thread starter AlisterN
  • Start date Start date
A

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
 
Hi, Alister.
I suspect I need to use ALTER TABLE with (possibly) ADD CONSTRAINT.
Click to expand...

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.


AlisterN said:
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
Click to expand...
 
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 said:
Hi, Alister.
I suspect I need to use ALTER TABLE with (possibly) ADD CONSTRAINT.
Click to expand...

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.
Click to expand...
[quoted text clipped - 11 lines]
Any help much appreciated.
Alister
Click to expand...
Click to expand...
 
Back
Top