none
How to modify this function? RRS feed

  • Question

  • I have the following function.

    Public Function dt_AccessTblRelationships(ByVal strCnn As String, _
                                              ByVal strTbl As String) _
                                              As DataTable
            'Purpose:       Find all relationships for the database table entered
            'Parameters:    strCnn As String - Properly built MS Access 
            '                                  connection string
            '               strTbl As String - Database table name whose
            '                                  relationships we are seeking.
            'Returns:       A DataTable od Relationships
            Dim schemaTable As DataTable
            Dim restrictions() As String
            Dim dr As DataRow
            Call dt_Remove_Rel()
            dt_Rel.Clear()
            '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"))
    
            Using cnn As New OleDbConnection(strCnn)
                Try
                    With cnn
                        .Open()
                        restrictions = {Nothing, Nothing, Nothing, _
                                        Nothing, Nothing, strTbl}
                        schemaTable = .GetOleDbSchemaTable( _
                                      OleDbSchemaGuid.Foreign_Keys, _
                                      restrictions)
                        .Close()
                        'Call dt_Read(schemaTable)
                        'Loop through the schemaTable 
                        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

    This functions returns all the relationships for the entered table, like so:

    In this example the table name sent to the function is tblCustomers. There are two tables that have foreign keys to the customers table. tblCountries with primary key CountryID and tblStates with primary key StateID. I know this has something to do with the restrictions parameter, but I haven't found any references on how to adjust this parameter. 

    restrictions = {Nothing, Nothing, Nothing, _
                    Nothing, strColumn, strTbl}

    Does anyone know where this reference is located?<sub></sub><sup></sup><strike></strike>

    Thanks,


    MRM256

    Friday, November 16, 2018 11:01 PM

All replies

  • Hi,

    see the following link,i hope this is what you need.

    http://www.iwms.net/trans.aspx?id=1973

    Best Regards,

    Alex


    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.

    Monday, November 19, 2018 1:52 AM
  • Hi Alex,

    This isn't want I need.

    I need some kind of reference that tells me how to accomplish specific things.

    I am modifying an application that builds data entry forms based on the schema of the selected table. If a column in the field is a secondary key. I want to replace the text box with a functional combo box.

    The form is constructed column by column. Therefore, when I hit a column that is a secondary key. The application reads the table and column names and uses that information to construct a query to populate the combo box. 

    Example: When the application sees the column CountryID. It takes the primary table name(tblCountries) and the column name(CountryID) derived from the relationships function and sends this information into another function to return a data source back to the newly created combo box. 

    Maybe this image of a dynamically created form will help give you an idea of what I am trying to accomplish.

    Does this help?


    MRM256

    Tuesday, November 20, 2018 3:08 PM