Member Avatar for rcostheta

Hello,
I am trying to pull the relationships from an Access database. I would like to know which fields have what type of relation with other tables in the database. Does anyone know how to do this?

Thanks,
Ben

  • 1 Contributor
  • 1 Reply
  • 124 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by rcostheta

Dani AI

Generated

Nice, — using the OLE DB schema rowset is the simplest .NET approach for a quick list of relationships. The provider returns a DataTable describing PK/FK pairs (things like PK_TABLE_NAME, PK_COLUMN_NAME, FK_TABLE_NAME, FK_COLUMN_NAME, UPDATE_RULE, DELETE_RULE, KEY_SEQ, FK_NAME). That is usually enough to display or bind to a DataGridView and inspect which columns participate in each foreign-key relationship.

If deeper Access-specific details are needed (relation attributes, cascade options, relation names, multi-column relations as stored by Access), consider these alternatives:

  • DAO (the Database.Relations collection) — native to Access and exposes the Relation.Attributes and Relation.Fields collections.
  • MSysRelationships system table — contains raw relationship metadata (requires showing system objects and appropriate permissions).
  • ADOX/COM interfaces — another route from .NET if you prefer ADOX over DAO.

Quick VBA/DAO example to enumerate relations (useful for understanding what Access stores):

' Reference: Microsoft DAO x.x Object Library
Dim db As DAO.Database
Dim rel As DAO.Relation
Set db = DBEngine.OpenDatabase("C:\path\to\file.mdb")
For Each rel In db.Relations
    Debug.Print rel.Name, rel.Attributes
    Dim f As DAO.Field
    For Each f In rel.Fields
        Debug.Print "  ", f.Name
    Next
Next

Troubleshooting tips: if the schema call returns nothing or incomplete rows, verify the connection string and provider (ACE OLEDB for .accdb, Jet for .mdb), confirm the exact file you opened (no duplicates or temporary copies), check for linked tables (relationships to linked sources may not appear as expected), and ensure relationships are defined at the database level (application logic won’t show up). If you need cascade/delete/update flags or Access-specific attributes, prefer DAO or MSysRelationships over the generic OLE DB schema rowset.

Member Avatar for rcostheta

Fairly simple answer to this (pseudo VB.net code, just get connected and create a data grid view):

Dim dt As DataTable

     dt =conn.GetOleDbSchemaTable( _
     System.Data.OleDb.OleDbSchemaGuid.Foreign_Keys,Nothing)

     DataGridView1.DataSource = dt
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.