Altering validation rules for all fields in a table via VBA

[alevol]

Hi all.

I'm new to Access and VBA. I have to change the validation rules of every field (except the record ID) in several tables that have about 200 fields each (they were made to for entering the answers of research questionnaires). I'm thinking the best way to do this is with a VB script. I found this on a microsoft developers website and was hoping it can be adapted to cycle through all the fields in a table and change the validation rule property (which will always be the same for all the fields) and was hoping someone might be able to help me out with how to do this. Thanks a lot!

Dim strTblName As String, strFldName As String
Dim strValidRule As String
Dim strValidText As String, intX As Integer

strTblName = "APQ"
strFldName = "Question1"
strValidRule = ">=1 And <=5"
strValidText = "Please use a value between 1 and 5."
intX = SetFieldValidation(strTblName, strFldName, _
strValidRule, strValidText)

Function SetFieldValidation(strTblName As String, _
strFldName As String, strValidRule As String, _
strValidText As String) As Integer

Dim dbs As Database, tdf As TableDef, fld As Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTblName)
Set fld = tdf.Fields(strFldName)
fld.ValidationRule = strValidRule
fld.ValidationText = strValidText
End Function

 

[DCrake]

The basic concept is correct however to do this for all tables here are the things you need to consider.

1. Create a loop that loops though all the tables in your database
1.1 only apply the changes to tables that are not system tables MSys or USys.

2. Create a loop that loops for all the fields in each of the tables
2.1 Check what type of field it is - Numeric
2.2 Not field(0) if the first field is the primary key or autonumber field

3. If the field is a number field does it already have a validation rule?
4. If so does it need changing - compare with new rule
5. change rule if needed.