none
Retrieving MS-Access table relationship contraints. RRS feed

  • Question

  • Hi Everyone,

    In SQL Server you can use a query to return which tables are related to the source table. Using a predefined set of tables stored inside the dbo.

    I want to do something similar using the OpenSchema or DataReader; I am not sure which  property for an MS-Access database. VS 2013 will not allow you to query any of Access' MSYS tables.

    Here is what I want to do.

    I have a table tblContractors that is related to two others: tblStates and tblUsers.

    I want to place the relationship constraints into a DataGridView control something like this:

    Table

    Related Tables

    Relationship Constraints

    tblContractor

    tblStates

    tblContractors.StateID --> tblStates.StateID

    tblContractor

    tblUsers

    tblContractors.UserID -->tblUsers.UserID

     

    Is there a way to do this?

    Thanks in advance,


    MRM256

    Thursday, August 10, 2017 2:44 PM

Answers

  • You can use GetOleDbSchemaTable. The below code puts the results in a DataGridView so you can see the column names returned:

            Dim AccessConnection As System.Data.OleDb.OleDbConnection
            Dim AccessDataTable As DataTable
    
            Try
    
                AccessConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                     "Data Source=C:\Test Files\db1 XP.mdb")
    
                AccessConnection.Open()
                Dim restrictions() As String = {}
                AccessDataTable = AccessConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, restrictions)
    
                Form3.DataGridView1.DataSource = AccessDataTable
    
                Dim RowCount As Int32
                Console.WriteLine(AccessDataTable.Rows.Count)
                For RowCount = 0 To AccessDataTable.Rows.Count - 1
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!PK_TABLE_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!PK_COLUMN_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!FK_TABLE_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!FK_COLUMN_NAME.ToString)
                Next RowCount
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
    
            End Try


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by MRM256 Monday, August 14, 2017 10:54 PM
    Thursday, August 10, 2017 4:43 PM

All replies

  • You can use GetOleDbSchemaTable. The below code puts the results in a DataGridView so you can see the column names returned:

            Dim AccessConnection As System.Data.OleDb.OleDbConnection
            Dim AccessDataTable As DataTable
    
            Try
    
                AccessConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                     "Data Source=C:\Test Files\db1 XP.mdb")
    
                AccessConnection.Open()
                Dim restrictions() As String = {}
                AccessDataTable = AccessConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, restrictions)
    
                Form3.DataGridView1.DataSource = AccessDataTable
    
                Dim RowCount As Int32
                Console.WriteLine(AccessDataTable.Rows.Count)
                For RowCount = 0 To AccessDataTable.Rows.Count - 1
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!PK_TABLE_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!PK_COLUMN_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!FK_TABLE_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!FK_COLUMN_NAME.ToString)
                Next RowCount
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
    
            End Try


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by MRM256 Monday, August 14, 2017 10:54 PM
    Thursday, August 10, 2017 4:43 PM
  • Hi Paul,

    This almost works, but I need it to return relationships for a single table. If I could use the MSysRelationships table in Access it would be simple: "SELECT MSysRelationships.* FROM        MSysRelationships WHERE (((MSysRelationships.szObject) = [strTbl]));

    However, your routine returns ALL relationships for the database, which isn't a bad thing. It could be used in the tool I am designing, but I want to keep confusion down to a minimum.

    I am not all that savvy using the Framework objects.

    Your thoughts.

    Thanks,


    MRM256

    Thursday, August 10, 2017 5:37 PM
  • You can filter the table name using the third parameter of the Restrictions:

                AccessDataTable = AccessConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, {Nothing, Nothing, "TBL1", Nothing})


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, August 10, 2017 10:01 PM
  • FYI, the below link should provide some information on how to use GetOleDbSchemaTable:

    How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual Basic .NET


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, August 10, 2017 10:09 PM
  • Hi Paul,

    This looks promising, but the syntax is strange.

    This command schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, _
                  New Object() {"Northwind", "dbo", "Employees"}) is to show the primary key field of the Employees table from the Northwind database.

    Okay I want to use variables instead of static string values. So I rewrote it like so:

    schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, _
                  New Object() {strDB, "dbo", strTbl}). Where the strDB is the connection string containing the database name and the strTbl is the table who I want to return the primary key.

    I get this error when the command is modified as shown above:  The parameter is incorrect

    This is what ticks me off about this "managed" coding. What could easily be done using a SQL query is complicated to the point something as simple as replacing static values with variables won't work.

    I modified my function to take variables:

    Public Function dt_AccessTblRelationships(ByVal strCnn As String, _
                                                  ByVal strShrDB As String, _
                                                  ByVal strTbl As String) _
                                                  As DataTable
            'Dim strDbName As String
            Dim schemaTable As DataTable
            Using cnn As New OleDbConnection(strCnn)
                Try
                    With cnn
                        .Open()
                        schemaTable = .GetOleDbSchemaTable( _
                                            OleDbSchemaGuid.Primary_Keys, _
                                            New String() {strShrDB.ToString, _
                                                            "dbo", strTbl.ToString})
                        'Debug Purposes
                        Call dt_Read(schemaTable)
                    End With
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                    Return Nothing
                End Try
            End Using
        End Function
    It looks like the methods are not designed to work with variables. I consider this a serious flaw in a programming language. Is there a work-around?

    Your thoughts please,


    MRM256



    • Edited by MRM256 Friday, August 11, 2017 5:00 PM Included modified code
    Friday, August 11, 2017 3:52 PM
  • Why don't you use the code I provided? My understanding was that you were using Access and not SQL Server. SQL Server will be different. You should only need the table name for Microsoft Access.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, August 11, 2017 6:46 PM
  • Paul,

    I placed Debug statements into the code to see what is being returned.

    Results:

    PK_TABLE_CATALOG 
    PK_TABLE_SCHEMA 
    PK_TABLE_NAME MSysNavPaneGroupCategories
    PK_COLUMN_NAME Id
    PK_COLUMN_GUID 
    PK_COLUMN_PROPID 
    FK_TABLE_CATALOG 
    FK_TABLE_SCHEMA 
    FK_TABLE_NAME MSysNavPaneGroups
    FK_COLUMN_NAME GroupCategoryID
    FK_COLUMN_GUID 
    FK_COLUMN_PROPID 
    ORDINAL 1
    UPDATE_RULE CASCADE
    DELETE_RULE CASCADE
    PK_NAME Id
    FK_NAME MSysNavPaneGroupCategoriesMSysNavPaneGroups
    DEFERRABILITY 
    PK_TABLE_CATALOG 
    PK_TABLE_SCHEMA 
    PK_TABLE_NAME MSysNavPaneGroups
    PK_COLUMN_NAME Id
    PK_COLUMN_GUID 
    PK_COLUMN_PROPID 
    FK_TABLE_CATALOG 
    FK_TABLE_SCHEMA 
    FK_TABLE_NAME MSysNavPaneGroupToObjects
    FK_COLUMN_NAME GroupID
    FK_COLUMN_GUID 
    FK_COLUMN_PROPID 
    ORDINAL 1
    UPDATE_RULE CASCADE
    DELETE_RULE CASCADE
    PK_NAME Id
    FK_NAME MSysNavPaneGroupsMSysNavPaneGroupToObjects
    DEFERRABILITY 
    PK_TABLE_CATALOG 
    PK_TABLE_SCHEMA 
    PK_TABLE_NAME tblStates
    PK_COLUMN_NAME StateID
    PK_COLUMN_GUID 
    PK_COLUMN_PROPID 
    FK_TABLE_CATALOG 
    FK_TABLE_SCHEMA 
    FK_TABLE_NAME tblContractors
    FK_COLUMN_NAME StateID
    FK_COLUMN_GUID 
    FK_COLUMN_PROPID 
    ORDINAL 1
    UPDATE_RULE NO ACTION
    DELETE_RULE NO ACTION
    PK_NAME PrimaryKey
    FK_NAME tblStatestblContractors
    DEFERRABILITY 
    PK_TABLE_CATALOG 
    PK_TABLE_SCHEMA 
    PK_TABLE_NAME tblUsers
    PK_COLUMN_NAME UserID
    PK_COLUMN_GUID 
    PK_COLUMN_PROPID 
    FK_TABLE_CATALOG 
    FK_TABLE_SCHEMA 
    FK_TABLE_NAME tblContractors
    FK_COLUMN_NAME UserID
    FK_COLUMN_GUID 
    FK_COLUMN_PROPID 
    ORDINAL 1
    UPDATE_RULE NO ACTION
    DELETE_RULE NO ACTION
    PK_NAME PrimaryKey
    FK_NAME tblUserstblContractors
    DEFERRABILITY 

    Am I correct in assuming that I just want the values for PK_TABLE_NAME, PK_COLUMN_NAME, FK_TABLE_NAME, FK_COLUMN_NAME. 

    Example: Restrictions = {"PK_TABLE_NAME", "PK_COLUMN_NAME", "FK_TABLE_NAME", "FK_COLUMN_NAME"} 


    MRM256

    Friday, August 11, 2017 9:23 PM
  • If you look at my code, where I'm looping through the data rows, you just need to check those columns. You don't need to add any restrictions, other than the table name - if you just want to check the relationships for a specific table. As a matter of fact, you probably just need the PK_TABLE_NAME and the FK_TABLE_NAME for each row that is returned.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, August 12, 2017 1:37 AM
  • Hi Paul,

    That is exactly what I want. I want to send the name of a table as a variable into the method and return the table names and the primary keys that are related to the table. The unrestricted method returns eighteen columns for four tables.  I don't know why, but I got eighteen columns of data for two Access System tables: MSysNavPaneGroupCatCategories and MSysNavPaneGroups.

    In the example I have been using. I send in the name of the table - tblContractors. The foreign keys in that table are StateID and UserID.  What I want returned in the DataTable is something like:

    Source Table

    Foreign  Key

    Related Table

    Primary Key

    tblContractors

    UserID

    tblUsers

    UserID

    tblContractors

    StatesID

    tblStates

    StateID

     

    What I don't understand properly is the restrictions array. How does it work? Most of the GetSchema Methods have either three or four restrictions. I have no idea what each element restricts from the methods. I have looked at the MSDN help, but I haven't had any flashes of insight on how they work.

    Any insight you can provide will be appreciated.

    Thanks,


    MRM256

    Saturday, August 12, 2017 3:14 PM
  • The documentation in the link I posted is rather clear with respect to the Restrictions:

    "Restrictions are an Object array of filter values, each of which corresponds to a DataColumn in the resulting DataTable. The OleDbSchemaGuid argument determines the relevant restrictions. For example, when you specify an OleDbSchemaGuid of tables, the array of restrictions is as follows:

    {TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}"

    As I've mentioned before, you only need to specify the table name for your query, nothing else.

    From the rows of results returned in the DataTable you can create your own DataTable in whatever format you desire. If you don't know how to create a new DataTable then see the documentation below:

    https://msdn.microsoft.com/en-us/library/9ha04z01(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, August 12, 2017 3:48 PM
  • Just an addendum, please ignore the system tables. There are of no use to you and you should not be attempting to use them to retrieve this information. You should only be specifying the table name(s) (in Restrictions) for which you want to retrieve the relationship information.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, August 12, 2017 3:53 PM
  • Paul,

    Just when I think I understand what is happening I get thrown for another loop.

    Here is the Function as it stands now:

    Public Function dt_AccessTblRelationships(ByVal strCnn As String, _
                                                  ByVal strTbl As String) _
                                                  As DataTable
            'Dim strDbName As String
            Dim schemaTable As DataTable
            Dim restrictions() As String
    
            Using cnn As New OleDbConnection(strCnn)
                Try
                    With cnn
                        .Open()
                        restrictions = {Nothing, Nothing, _
                                        strTbl, Nothing}
                        schemaTable = .GetOleDbSchemaTable( _
                                      OleDbSchemaGuid.Foreign_Keys, _
                                      restrictions)
    
                        'Debug Purposes
                        Call dt_Read(schemaTable)
                    End With
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                    Return Nothing
                End Try
            End Using
            Return schemaTable
        End Function

    I have another subroutine I use to visually see what information I am returning.

     Public Sub dt_Read(ByVal dt As DataTable)
            'Purpose:       Lets the programmer see the data
            '               contained in the DataTable
            'Parameters:    dt As DataTable
            'Returns:       Nothing - Just a Debug routine
    
            Dim myRow As DataRow
            Dim myCol As DataColumn
            'For each field in the table...
            For Each myRow In dt.Rows
                'For each property of the field...
                For Each myCol In dt.Columns
                    'Display the field name and value.
                    Debug.Print(myCol.ColumnName & vbTab & _
                                 myRow(myCol).ToString())
                    'Debug.Print(vbTab & myField(myProperty).ToString())
                Next
            Next
        End Sub

    Every thing is porting to the dt_AccessTblRelationships function okay, but when it runts through the debug subroutine there are no Rows or Columns to show.

    Am I using the right GetSchema function, because I can't see what is causing the problem.

    Thanks


    MRM256

    Saturday, August 12, 2017 5:54 PM
  • Did you forget to initialize the strTbl parameter? If that is an empty string, you won't get any data returned to the schemaTable  DataTable.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Saturday, August 12, 2017 8:39 PM fix typo, clarity
    Saturday, August 12, 2017 8:37 PM
  • Hi Bonnie, 

    Your first time in the VB forums. :-) 

    Be aware that if it is about a Jet database (most call that Access) than I'm only giving answers as long as Paul Clement is not involved. :-)

    (That is not in the case of SQL server, by the way, and you would not do that either). 


    Success
    Cor


    Saturday, August 12, 2017 8:48 PM
  • Hi Cor,

    Yeah, you're right about the VB forums.  ;0)

    I know that Paul is the best guy to answer these questions about Access. However, Mark (the OP) had asked me about this in an email after he posted here, before anyone had replied. When I finally got around to looking at his post, Paul had already started helping him. So I told Mark I'd just monitor the thread as long as Paul was helping.

    But then, darn it, I saw Mark's last post and I *knew* I could help on that one! And it had been almost 3 hours and Paul hadn't been back yet. So, I thought I would go ahead and help out.

    All credit should go totally to Paul, though!!!!


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, August 12, 2017 8:59 PM
  • I copied your code and passed in a connection string and table name in the database specified in the connection string, and it works perfectly fined for me.

    How do you know there are no Rows? Did you look in the Immediate Window to see if anything was output? Also, did you specify a table that had a primary/foreign key relationship with another table?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, August 12, 2017 10:31 PM
  • Hi Bonnie, 

    Your first time in the VB forums. :-) 

    Be aware that if it is about a Jet database (most call that Access) than I'm only giving answers as long as Paul Clement is not involved. :-)

    (That is not in the case of SQL server, by the way, and you would not do that either). 


    Success
    Cor


    LOL Cor!

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, August 12, 2017 10:32 PM
  • Hi Bonnie,

    I checked the variable as it was passed into the function. It looked okay to me

    Can you see the problem?

    Thanks,


    MRM256

    Sunday, August 13, 2017 1:15 AM
  • Hi Paul,

    Your help has been much appreciated. I just could not see the problem when I set the restrictions on the modified Function. There is a screen shot of me stepping through the code on my reply to Bonnie.

    Bonnie mentioned "initialize the strTbl parameter". The strTbl variable showed "tblContractors" while I was stepping through the code. It looked initialized to me, or was it not? I have no idea.

    I am NOT using a JET database. My current version of MS-Access is 2010, and I know there is a different connection string.

    Here is the button event that sets the connection string:

    Private Sub btnAccessCnn_Click(sender As Object, _
                                       e As EventArgs) _
                                        Handles btnAccessCnn.Click
            'Purpose:       Connects to a MS-Access database
            'Parameters:    None
            'Results:       Populates the cboAccessTbls ComboBox
            '               with tables contained in the selected
            '               MS-Access database
            Dim dt As New DataTable
            strAccessCnn = "Provider=Microsoft.ACE.OLEDB.12.0"
            strAccessCnn += ";Data Source=" & txtDB.Text
            strAccessCnn += ";User Id=" & txtUser.Text
            strAccessCnn += ";Password="
            If txtPW.Text.Length > 0 Then
                strAccessCnn += txtPW.Text & ";"
            End If
            Me.cboAccessTbls.DataSource = Nothing
            Me.cboAccessTbls.DataSource = objAccessDB.AccessDBTbls(strAccessCnn)
        End Sub

    I know the connection string works, because I get the database tables listed in my ComboBox. 

    Here is the connection string during execution:

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Mark\Documents\Arduino\Database_Prjs\Hydra Project\Contractors_Data_be.accdb;User Id=Admin;Password=

    I retried reading the relationships DataTable once again right after you told me there was no problem on your end. I still have the same issue.

    It is hard trying to find a problem when everything seems to be working fine, but the debug routine shows no rows or columns in the DataTable.

    Thanks,


    MRM256

    Sunday, August 13, 2017 1:39 AM
  • OK, lets break this down. Set a breakpoint on the call to GetOleDbSchemaTable. Run your code and when the code breaks on that line, execute it and then check the value of schemaTable.Rows.Count and let me know what it is.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, August 13, 2017 12:21 PM
  • Hi Paul,

    I did as you suggested. I placed a breakpoint at the indicated line and right below that I added Debug.Print(schemaTable.Rows.Count). After I stepped pasted the breakpoint the schemaTable.Rows.Count value is ZERO(0).

    For some odd reason I think it has to do with how I am setting up the restrictions.

    Thanks,


    MRM256

    Sunday, August 13, 2017 1:22 PM
  • OK, after looking at your Relationships diagram again I think I see your issue. The primary/foreign key one-to-many relationship is in the opposite direction than what you have in your grid. You should be checking tblStates and tblUsers in order to identify those relationships instead since they have the primary keys and tblContractors has the foreign keys.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, August 13, 2017 1:30 PM
  • I think this brings us back to the beginning.

    I know the tblContractors has two foreign keys; StateID and UserID from the primary keys of tables tblStates and tblUser. 

    The question I am sending to the GetOleDbSchemaTable method is: I want to know which tables are related to tblContractors.

    Thanks,


    MRM256

    Sunday, August 13, 2017 1:42 PM
  • Looks like I found a shortcoming on the GetOleDbSchemaTable(                               OleDbSchemaGuid.Foreign_Keys method. It does not have the ability to do many-to-one relationships.

    I am trying to build a tool for legacy database manipulation. The user must be able to determine how the data tables are related.

    Thanks,


    MRM256

    Sunday, August 13, 2017 2:13 PM
  • I think this brings us back to the beginning.

    I know the tblContractors has two foreign keys; StateID and UserID from the primary keys of tables tblStates and tblUser. 

    The question I am sending to the GetOleDbSchemaTable method is: I want to know which tables are related to tblContractors.

    Thanks,


    MRM256


    You are correct, which is why I have to go back to my first example. If you want the foreign key to primary key relationship then you will have to remove the table filter in Restrictions, which will list all table relationships. Then you can loop through the rows and check the FK_TABLE_NAME to see if it's equal to your table. If it is then you can get the related table with the primary key from the PK_TABLE_NAME value.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, August 13, 2017 2:22 PM
  • Looks like I found a shortcoming on the GetOleDbSchemaTable(                               OleDbSchemaGuid.Foreign_Keys method. It does not have the ability to do many-to-one relationships.

    I am trying to build a tool for legacy database manipulation. The user must be able to determine how the data tables are related.

    Thanks,


    MRM256


    This is not true. Please read my last response.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, August 13, 2017 2:25 PM
  • Looks like I found a shortcoming on the GetOleDbSchemaTable(                               OleDbSchemaGuid.Foreign_Keys method. It does not have the ability to do many-to-one relationships.

    I am trying to build a tool for legacy database manipulation. The user must be able to determine how the data tables are related.

    Thanks,


    MRM256


    BTW, let me add that if you want more specifics about the table relationships (e.g. one-to-one or one-to-many, etc.), then you will need to use the DAO library and the Relation object. This information is not exposed by the Access OLEDB providers.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Sunday, August 13, 2017 3:04 PM
  • Paul,

    Sometimes my brain is like a White Dwarf: Too dense for anything to penetrate.

    Can the DAO library and the Relations object perform the operation I have in mind, i.e. given a table name list its relationships?

    As a fall back plan I can use the GetOleDbSchemaTable(                               OleDbSchemaGuid.Foreign_Keys method to list every table and how they are related. Cumbersome but doable.

    Thanks,


    MRM256

    Sunday, August 13, 2017 5:00 PM
  • Paul,

    Can the DAO library and the Relations object perform the operation I have in mind, i.e. given a table name list its relationships?

    The table name would need to be checked against the information returned from the Relation objects in the Relations collection, so no there is no direct query. I can dig up some code for that if you are interested.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, August 14, 2017 2:27 PM
  • Hi Paul,

    I went brain dead yesterday and this morning it finally dawned on me what you wanted me to do.

     Public Function dt_AccessTblRelationships(ByVal strCnn As String, _
                                               ByVal strTbl As String) _
                                               As DataTable
            'Dim strDbName As String
            Dim schemaTable As DataTable
            Dim restrictions() As String
    
            Dim dr As DataRow
            'Creating New Table 
            Dim dt_Rel As New DataTable
            Using cnn As New OleDbConnection(strCnn)
                Try
                    With cnn
                        .Open()
                        restrictions = {}
                        schemaTable = .GetOleDbSchemaTable( _
                                      OleDbSchemaGuid.Foreign_Keys, _
                                      restrictions)
                        .Close()
                        For RowCount = 0 To schemaTable.Rows.Count - 1
                            If InStr(UCase(schemaTable.Rows(RowCount)!PK_TABLE_NAME.ToString), "MSYS") Then
                                Continue For
                            Else
                                'dr = dt_Rel.NewRow
                                'dr.Item(RowCount) = schemaTable.Rows(RowCount)!PK_TABLE_NAME.ToString
                                'dr.Item(RowCount) = schemaTable.Rows(RowCount)!PK_COLUMN_NAME.ToString
                                'dr.Item(RowCount) = schemaTable.Rows(RowCount)!FK_TABLE_NAME.ToString
                                'dr.Item(RowCount) = schemaTable.Rows(RowCount)!FK_COLUMN_NAME.ToString
                                'dt_Rel.Rows.Add(dr)
                                Debug.Print(schemaTable.Rows(RowCount)!PK_TABLE_NAME.ToString)
                                Debug.Print(schemaTable.Rows(RowCount)!PK_COLUMN_NAME.ToString)
                                Debug.Print(schemaTable.Rows(RowCount)!FK_TABLE_NAME.ToString)
                                Debug.Print(schemaTable.Rows(RowCount)!FK_COLUMN_NAME.ToString)
                            End If
                        Next RowCount
                        'Debug Purposes
                        Call dt_Read(dt_Rel)
                    End With
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                    Return Nothing
                End Try
            End Using
            Return schemaTable
        End Function

    The debug statements bring back the information I want. This looks much better.

    PK_TABLE_NAME

    PK_COLUMN_NAME

    FK_TABLE_NAME

    FK_COLUMN_NAME

    tblStates

     

    StateID

     

    tblContractors

     

    StateID

     

    tblUsers

    UserID

    tblContractors

     

    UserID

    Now if I can just remember now to write that to a new DataTable for the DataGridView I will be set.

    BTW - This will return all relationships in the database. Not for a selected table.

    Your thoughts,


    MRM256

    Monday, August 14, 2017 2:53 PM
  • Paul,

    Finally got it working!!

    Here is the function:

    Public Function dt_AccessTblRelationships(ByVal strCnn As String, _
                                                  ByVal strTbl As String) _
                                                  As DataTable
            'Dim strDbName As String
            Dim schemaTable As DataTable
            Dim restrictions() As String
    
            Dim dr As DataRow
            'Creating New Table 
            Dim dt_Rel As New DataTable
            'Creating New columns for dt_Rel
            dt_Rel.Columns.Add("PK_Tbl_Name", _
                               Type.GetType("System.String"))
            dt_Rel.Columns.Add("PK_Col_Name", _
                               Type.GetType("System.String"))
            dt_Rel.Columns.Add("FK_Tbl_Name", _
                               Type.GetType("System.String"))
            dt_Rel.Columns.Add("FK_Col_Name", _
                               Type.GetType("System.String"))
            'Clear Relationships DataTable
            dt_Rel.Clear()
            Using cnn As New OleDbConnection(strCnn)
                Try
                    With cnn
                        .Open()
                        restrictions = {}
                        schemaTable = .GetOleDbSchemaTable( _
                                      OleDbSchemaGuid.Foreign_Keys, _
                                      restrictions)
                        .Close()
                        For RowCount = 0 To schemaTable.Rows.Count - 1
                            If InStr(UCase(schemaTable.Rows(RowCount)! _
                                           PK_TABLE_NAME.ToString), "MSYS") Then
                                Continue For
                            Else
                                dr = dt_Rel.NewRow
                                dr.Item("PK_Tbl_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    PK_TABLE_NAME.ToString
                                dr.Item("PK_Col_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    PK_COLUMN_NAME.ToString
                                dr.Item("FK_Tbl_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    FK_TABLE_NAME.ToString
                                dr.Item("FK_Col_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    FK_COLUMN_NAME.ToString
                                dt_Rel.Rows.Add(dr)
                            End If
                        Next RowCount
                        'Debug Purposes
                        'Call dt_Read(dt_Rel)
                    End With
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                    Return Nothing
                End Try
            End Using
            Return dt_Rel
        End Function

    Thank you so much for your guidance,


    MRM256

    • Marked as answer by MRM256 Monday, August 14, 2017 3:51 PM
    • Unmarked as answer by MRM256 Monday, August 14, 2017 11:05 PM
    Monday, August 14, 2017 3:51 PM
  • Thanks but I don't think you understand how this forum works. You give credit to those who have helped you by marking their responses as answers or by voting them helpful. After all the help you have received I consider marking your response as an answer to be disrespectful.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, August 14, 2017 6:03 PM
  • Paul,

    I'm sorry. You are right. I don't understand how the forum works. 

    However, because of you I figured out how to solve the problem and because it works I marked it as an answer.

    You did help me and I need you to know that. To prevent me from making anymore forum blunders. Please tell me how to vote you being helpful.

    Thanks,


    MRM256

    Monday, August 14, 2017 6:44 PM
  • Paul,

    I'm sorry. You are right. I don't understand how the forum works. 

    However, because of you I figured out how to solve the problem and because it works I marked it as an answer.

    You did help me and I need you to know that. To prevent me from making anymore forum blunders. Please tell me how to vote you being helpful.

    Thanks,


    MRM256

    Go through the various responses and "upvote" those that were helpful. To do that, notice at the top-left of each reply that there's a "0" and "Vote" beneath that.

    Click the delta symbol to upvote the post.


    "A problem well stated is a problem half solved.” - Charles F. Kettering

    Monday, August 14, 2017 7:31 PM
  • Mark (MRM256), I think that you need to mark at least one of Paul's replies as an answer (and a couple of the his other replies as being helpful). In fact, his very first reply turns out to have been the answer all along, pretty much ... don't you think?

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, August 14, 2017 8:06 PM
  • Understood, that happens.

    You can click on the number above "Vote" for a specific response if the information was helpful. You can also mark more than one post as an answer, for example if multiple people helped you and the information from their posts was used in your solution.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, August 14, 2017 10:01 PM
  • You can use GetOleDbSchemaTable. The below code puts the results in a DataGridView so you can see the column names returned:

            Dim AccessConnection As System.Data.OleDb.OleDbConnection
            Dim AccessDataTable As DataTable
    
            Try
    
                AccessConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                     "Data Source=C:\Test Files\db1 XP.mdb")
    
                AccessConnection.Open()
                Dim restrictions() As String = {}
                AccessDataTable = AccessConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, restrictions)
    
                Form3.DataGridView1.DataSource = AccessDataTable
    
                Dim RowCount As Int32
                Console.WriteLine(AccessDataTable.Rows.Count)
                For RowCount = 0 To AccessDataTable.Rows.Count - 1
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!PK_TABLE_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!PK_COLUMN_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!FK_TABLE_NAME.ToString)
                    'Console.WriteLine(AccessDataTable.Rows(RowCount)!FK_COLUMN_NAME.ToString)
                Next RowCount
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                AccessConnection.Close()
    
            End Try


    Paul ~~~~ Microsoft MVP (Visual Basic)


    This was the solution, but I was stuck on my own method and didn't see it.

    MRM256

    Monday, August 14, 2017 10:55 PM
  • FYI, the below link should provide some information on how to use GetOleDbSchemaTable:

    How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual Basic .NET


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Went to this link and used it to create a Function that returns Table Schema information


    MRM256

    Monday, August 14, 2017 11:01 PM
  • Paul,

    Finally got it working!!

    Here is the function:

    Public Function dt_AccessTblRelationships(ByVal strCnn As String, _
                                                  ByVal strTbl As String) _
                                                  As DataTable
            'Dim strDbName As String
            Dim schemaTable As DataTable
            Dim restrictions() As String
    
            Dim dr As DataRow
            'Creating New Table 
            Dim dt_Rel As New DataTable
            'Creating New columns for dt_Rel
            dt_Rel.Columns.Add("PK_Tbl_Name", _
                               Type.GetType("System.String"))
            dt_Rel.Columns.Add("PK_Col_Name", _
                               Type.GetType("System.String"))
            dt_Rel.Columns.Add("FK_Tbl_Name", _
                               Type.GetType("System.String"))
            dt_Rel.Columns.Add("FK_Col_Name", _
                               Type.GetType("System.String"))
            'Clear Relationships DataTable
            dt_Rel.Clear()
            Using cnn As New OleDbConnection(strCnn)
                Try
                    With cnn
                        .Open()
                        restrictions = {}
                        schemaTable = .GetOleDbSchemaTable( _
                                      OleDbSchemaGuid.Foreign_Keys, _
                                      restrictions)
                        .Close()
                        For RowCount = 0 To schemaTable.Rows.Count - 1
                            If InStr(UCase(schemaTable.Rows(RowCount)! _
                                           PK_TABLE_NAME.ToString), "MSYS") Then
                                Continue For
                            Else
                                dr = dt_Rel.NewRow
                                dr.Item("PK_Tbl_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    PK_TABLE_NAME.ToString
                                dr.Item("PK_Col_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    PK_COLUMN_NAME.ToString
                                dr.Item("FK_Tbl_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    FK_TABLE_NAME.ToString
                                dr.Item("FK_Col_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    FK_COLUMN_NAME.ToString
                                dt_Rel.Rows.Add(dr)
                            End If
                        Next RowCount
                        'Debug Purposes
                        'Call dt_Read(dt_Rel)
                    End With
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                    Return Nothing
                End Try
            End Using
            Return dt_Rel
        End Function

    Thank you so much for your guidance,


    MRM256


    This function can about after I realized what Paul was getting at in his first post.

    MRM256

    Monday, August 14, 2017 11:06 PM
  • Thanks but I don't think you understand how this forum works. You give credit to those who have helped you by marking their responses as answers or by voting them helpful. After all the help you have received I consider marking your response as an answer to be disrespectful.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Taught me about forum etiquette.

    MRM256

    Monday, August 14, 2017 11:14 PM
  • Hi Bonnie,

    Yes, I went through the tread and marked Paul's first response as the answer, and the others that were helpful. Including the one about my lack of forum etiquette.

    Hopefully, I have taken care of all my blunders.


    MRM256

    Monday, August 14, 2017 11:19 PM
  • Including the one about my lack of forum etiquette.

    Hopefully, I have taken care of all my blunders.

    LOL!

    Every type of forum has it's own etiquette. In some forums, the accepted answer is determined by how many votes it gets (like the way that "StackOverflow" works), some forums determine the accepted answer if someone clicks on Yes on "Did this resolve your issue?" (like the way "Answers" works), some forums don't have a way of determining the accepted answer and, lastly, some forums have no moderation and degrade into knock-down, drag-out fighting!! Fun!  ;0)

    So, don't fret about your blunders ... now you know the forum etiquette here!!  =0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, August 14, 2017 11:41 PM