none
Can we refine the MS-Access table relationships? RRS feed

  • Question

  • Hi Everyone,

    Paul P. Clement IV gave me a solution for my thread "Retrieving MS-Access Table Relationship Constraints" and it worked fine for a simple database. However, it gets much more cumbersome when your database has a butt load of relationships.

    I was wondering is there a way to get the relationships for a single table?

    Let's say I want the foreign keys for tblJobs. According to the diagram above I have two foreign keys called JobID in the tblJobsDetails, tblJobDetails_1 related to the primary key tblJobs.JobID. A tblJobs.CustID foreign key related to tblCustomers.CustID and so on. In total I have seven foreign keys relating to seven other tables.

    Thoughts,


    MRM256

    Tuesday, August 22, 2017 11:23 PM

Answers

  • If you want the foreign key columns for tblJobs, they would be for the following:

    BusinessUnitID
    ProductID
    StatusID
    PymtID
    CustID (if it's linked to the primary key CustID in tlbCustomers)

    These are part of a foreign key because they are linked to a primary key in another table.

    The code would look something like this:

    AccessDataTable = AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Foreign_Keys, New Object() {Nothing, Nothing, Nothing, Nothing, Nothing, "tblJobs"})


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by MRM256 Wednesday, August 23, 2017 2:49 PM
    Wednesday, August 23, 2017 12:46 PM

All replies

  • Hi MRM256,

    Based on your description, you just want to query one table relationship, I find one thread that discussing the same issue, you can refer to:

    https://stackoverflow.com/questions/8100018/retrieve-table-relationships-from-ms-access-db-using-c-sharp

    The thread use C#, but you can use any one convert tool to convert into vb.net.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 23, 2017 8:23 AM
    Moderator
  • This kind of databases you would not want do with MS - Access

    Take for instance SQL Server 

    https://www.microsoft.com/en-us/sql-server/sql-server-editions-express

    It gives you the chance to use the Entitity Framework. 

    If you want for whatever reason to stay with MS -Acces (be aware it is a file not a server), then you can use the Table AdapterManager.

    https://msdn.microsoft.com/en-us/library/bb384426.aspx

    Be aware that setting your relations in code would mean that you would as single person be at least busy until 2020. 

    We have VB samples on our website for that, but I won't show it to you to avoid somebody thinks a database like yours could be done with that. 


    Success
    Cor


    • Edited by Cor Ligthert Wednesday, August 23, 2017 10:06 AM
    Wednesday, August 23, 2017 10:06 AM
  • If you want the foreign key columns for tblJobs, they would be for the following:

    BusinessUnitID
    ProductID
    StatusID
    PymtID
    CustID (if it's linked to the primary key CustID in tlbCustomers)

    These are part of a foreign key because they are linked to a primary key in another table.

    The code would look something like this:

    AccessDataTable = AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Foreign_Keys, New Object() {Nothing, Nothing, Nothing, Nothing, Nothing, "tblJobs"})


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by MRM256 Wednesday, August 23, 2017 2:49 PM
    Wednesday, August 23, 2017 12:46 PM
  • That's exactly what I want. Just the tables related to the selected one.

    Thanks,


    MRM256

    Wednesday, August 23, 2017 2:54 PM
  • Yes, the Restrictions object is very poorly documented. I was never was aware that there were more than four parameters until I did a little more research on it.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, August 23, 2017 4:35 PM