How to remove a "Validation Rule" via DDL

[sblocher]

Does anyone know how to use a DDL command to remove or change a field's "Validation Rule" in an Access table?

I have an existing MDB (2002) with a table that was created using the Access table designer. Table is named 'GlidePath', with a field named 'DaysOnly'. The 'Validation Rule' property of the table was set to "MW" or "TF" (also using the designer) to restrict values in that field to MW or TF. But I need to change or remove that restriction. And i don't want to have to open the MDB using Access.

I tried the following:

ALTER TABLE GlidePath DROP CONSTRAINT [DaysOnly.ValidationRule]

but Jet is returning an error "CHECK constraint 'DaysOnly.ValidationRule' does not exist."

(I am using a linked server in SQL Server to attach to the database, and use DDL to modify it. I can run other DDL commands against it, but can't remove or modify the "Valiation Rule".)

 

[Golom]

CurrentDB.TableDefs("GlidePath").Fields("DaysOnly").Properties("ValidationRule") = ""

Assuming you're using DAO.

 

[sblocher]

No, not using DAO. Using SQL DDL.

 

[Golom]

Unfortunately there are a lot of things about objects in a DBMS that SQL DDL knows nothing about. Here, for example are some of the properties of a specific field in an access database that need to be manipulated by the features of the Access technology rather than through SQL.
[tt]
[red]DAO Properties[/red]
Attributes 2
CollatingOrder 1033
Type 10
Name Reason
OrdinalPosition 4
Size 5
SourceField Reason
SourceTable StockAdjustments
DataUpdatable False
DefaultValue RC
ValidationRule In ('ST','SL','SZ','RT','TR','WO','RC')
ValidationText
Required False
AllowZeroLength True
ColumnWidth -1
ColumnOrder 0
ColumnHidden False
Description Reason For Adjustment - 'RC' = Received; 'ST' = Stock Take; 'TR' = Stock Transfer
DisplayControl 109
IMEMode 0
IMESentenceMode 3
UnicodeCompression True


[red]ADOX Properties[/red]
Autoincrement False
Default RC
Description Reason For Adjustment - 'RC' = Received; 'ST' = Stock Take; 'TR' = Stock Transfer
Nullable True
Fixed Length False
Seed 1
Increment 1
Jet OLEDB:Column Validation Text
Jet OLEDB:Column Validation Rule In ('ST','SL','SZ','RT','TR','WO','RC')
Jet OLEDB:IISAM Not Last Column False
Jet OLEDB:AutoGenerate False
Jet OLEDB:One BLOB per Page False
Jet OLEDB:Compressed UNICODE Strings True
Jet OLEDB:Allow Zero Length True
Jet OLEDB:Hyperlink False
[/tt]

You will need to use the features of some data access technology such as DAO or ADO if you want to change them in code.

 

[sblocher]

Thanks. Is there an MS KB article that lists the properties you cannot get to or modify with DDL?

 

[Golom]

Not that I know of.

The problem is that there is no fixed list of properties that apply to every field ... and properties are a function of the DBMS you are using. For example, an autonumber field has a "seed" property but that doesn't apply to a Text field so it doesn't have that property. Additionally, you can add your own properties to the properties collection.

If you want to see the properties for a field then run this bit of code

Private Sub Command5_Click()
    Dim db                          As DAO.Database
    Dim prp                         As DAO.Property
    
    Set db = DAO.DBEngine(0).OpenDatabase("SomeDatabase.mdb")
    
    On Error Resume Next
    
    For Each prp In db.TableDefs("myTable").Fields("SomeField").Properties
        Debug.Print prp.Name & "     " & prp.Value
    Next
    Set db = Nothing
End Sub