locked
How to access MSysRelationships table of ms-access 2007 from .net? RRS feed

  • Question

  • I want to get all table relations defined in MS-Access 2007 (accdb) database in .net code. By using following code, I am able to get the tables and columns involved to create PK-FK relationship.

    oleconnection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Key,restrictions)

    There problem is that there are more attributes in access database for a relation i.e. Enforce Referential Intigrity, Cascade Update Related Fields, Cascade Delete related Fields.

    I am not able to check for a relation whether the Referential Intigrity is on or not.

    I found the MSysRelationships  table that holds the information related to relations and I can use grbit field of this table to get all these information. 

    Now tell me, how I can access MSysRelationships  table from .Net code or fetch the extra attributes for a relation using GetOleDbSchemaTable.

    Please remember I need to use Access 2007/2010 and database accdb (not mdb).

    Friday, June 15, 2012 10:35 AM

Answers

  • Hi Romil,

    Welcome to the MSDN Forum.

    Please try to code in the original post: http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/91cdf47e-0aa2-4c47-b04d-7909c716367f 

    Dim c As New OleDb.OleDbCommand("Select * From MSysRelationships", connection)
          connection.Open()
    Dim r = c.ExecuteReader
    
    Dim fks As New Dictionary(Of String, String)
    While r.Read
      fks.Add(r("szRelationship"), fks.Count & "_" & _
                Left(r("szReferencedObject"), 7) & _
                Left(r("szObject").ToString, 7) & _
                Left(r("szColumn").ToString, 7) & _
                Left(r("szReferencedColumn").ToString, 7))
    End While

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Mike Feng Monday, June 25, 2012 11:40 AM
    Monday, June 18, 2012 6:04 AM