none
Retrieving Constraints from an Access Database RRS feed

  • Question

  • I'm working with a C# Application that relies on an ancient Access database.  We're moving toward trying to get things in to XML, but due to the sheer size of it we really need the process automated.

     

    The Access database stores the Constraints (Validation Rules) in the database itself for the columns.  We really need to retrieve this information.

     

    What I'm finding right now is no clear way to access this.  I've tried using the code:

    Code Snippet

    DataGridView dg = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Table_Constraints, null);

     

     

    but doing that all I get is a data grid that's literally filled with the constraints themselves, but none of the actual data.  I tried some of the other options, but those didn't work.

     

    In C++, I found using the _FieldPtr type worked perfect because you could call its ValidationRule method and get them, but that really isn't ideal for this situation.

     

    Any thoughts?

     

    Certainly let me know if I can explain or provide additional details!

    Thursday, September 6, 2007 8:28 PM

Answers

  • I'm not sure whether this information is available using GetOleDbSchemaTable. I know that it can be retrieved using DAO or ADOX:

     

    ADOX:

    cat.Tables("TableName").Columns("ColName").Properties("Jet OLEDB:Column Validation Rule")

     

    DAO:

    TableDefs("TableName").Fields("ColName").Properties("ValidationRule").Value

     

    Friday, September 14, 2007 4:26 PM

All replies

  • To further clarify on this, I am specifically looking to get the specific SQL Style WHERE clause that they store in there.  I can already find out what the constraints are, but i can't figure out the rest.

    Wednesday, September 12, 2007 3:19 PM
  • Could you indicate specifically which constraints you're referring to? This is kind of a broadly used term and it might help if you could clarify it with respect to Microsoft Access terms.

    Wednesday, September 12, 2007 5:22 PM
  • In access it appears they're called Validation Rules.  For example, I have one that says "Between 0 And 2147483647" and I basically just need to get that.  This is for the table when I select a specific column in the "Design View" for that table.

     

    If I can get that, I can take whatever steps next to work with it.  I just want that Between 0 And 2147483647.

    Wednesday, September 12, 2007 5:48 PM
  • I'm not sure whether this information is available using GetOleDbSchemaTable. I know that it can be retrieved using DAO or ADOX:

     

    ADOX:

    cat.Tables("TableName").Columns("ColName").Properties("Jet OLEDB:Column Validation Rule")

     

    DAO:

    TableDefs("TableName").Fields("ColName").Properties("ValidationRule").Value

     

    Friday, September 14, 2007 4:26 PM
  •  

    Thanks, that's perfect.  I don't know about the Ole, but it looks like I can just rewrite everything to use ADOX.

    Friday, September 14, 2007 7:05 PM