none
Need a work around RRS feed

  • Question

  • Hi Everyone,

    I have this function:

    Public Function dt_AccessTblRelationships(ByVal strCnn As String, _
                                                  ByVal strTbl As String) _
                                                  As DataTable
            Dim schemaTable As DataTable
            Dim restrictions() As String
            Dim dr As DataRow
            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"))
            'Clear Relationships DataTable
    
            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

    It works, but has a flaw and I am having trouble finding a work-around.

    I modified my application, so the user can right-click on the active tab page and it returns only the relationships necessary for that table.

    Example:


    If I have other pages that I want to see relationships for. The Function fails, because it is trying to recreate the dt_Rel data table's rows.

    One way to fix it would be just return the data in the four columns needed; PK_TABLE_NAME, PK_COLUME_NAME, FKTABLE_NAME and FK_COLUMN_NAME from the schemaTable variable. Another would be to create the dt_Rel table at the Form_Load Event and clear it each time.

    I know someone has a more elegant way to accomplish this.

    Ideas?

    Thank you


    MRM256

    Sunday, August 27, 2017 7:17 PM

Answers

  • Hi MRM256,

    Based on your description, you use dt.rows.clear() to clear the datatable's data, but you don't remove its columns' name, so you encounter this issue. I think you should remove columns' name, like this:

     Public dt As New DataTable
        Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
                    dt.Columns.Add("Column1", GetType(Int32))
            dt.Columns.Add("Column2", GetType(String))
            dt.Columns.Add("Column3", GetType(String))
            dt.Rows.Add(1, "AAA", "aaa")
            dt.Rows.Add(2, "BBB", "bbb")
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            dt.Rows.Clear()
            Dim columncount As Integer = dt.Columns.Count - 1
            For i = columncount To 0 Step -1
                dt.Columns.RemoveAt(i)
            Next
        End Sub

    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.

    • Marked as answer by MRM256 Monday, August 28, 2017 10:06 PM
    Monday, August 28, 2017 9:23 AM
    Moderator

All replies

  • Hi MRM256,

    Based on your description, you use dt.rows.clear() to clear the datatable's data, but you don't remove its columns' name, so you encounter this issue. I think you should remove columns' name, like this:

     Public dt As New DataTable
        Private Sub Form6_Load(sender As Object, e As EventArgs) Handles MyBase.Load
                    dt.Columns.Add("Column1", GetType(Int32))
            dt.Columns.Add("Column2", GetType(String))
            dt.Columns.Add("Column3", GetType(String))
            dt.Rows.Add(1, "AAA", "aaa")
            dt.Rows.Add(2, "BBB", "bbb")
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            dt.Rows.Clear()
            Dim columncount As Integer = dt.Columns.Count - 1
            For i = columncount To 0 Step -1
                dt.Columns.RemoveAt(i)
            Next
        End Sub

    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.

    • Marked as answer by MRM256 Monday, August 28, 2017 10:06 PM
    Monday, August 28, 2017 9:23 AM
    Moderator
  • I know that you have an very huge detailed database. 

    As I said before, in they way you try it you are not ready with coding before 2020.

    However, any help to you to bring you on the right road is vain. 

    I know your style of code was done in 1990, in 1890 they used horses but those where never used to go to the moon although there was no problem to go to the next ranch.


    Success
    Cor


    Monday, August 28, 2017 12:26 PM
  • Hi Cor,

    This is a database tool. It allows you to look at specific tables and if necessary the related ones.

    If you look at the sample I provided tblJobs is the main one, and the two additional tables store specific details about the job order. There is a bunch of queries used throughout the system for list and combo boxes. These queries were used to help the person taking the orders and kept error down.

    There are also a slew of supporting tables. The JETS(Job Entry Tracking System) was written in MS-Access 2005 as a job orders tracking system. Therefore, I had to have a bunch of tables that could be modified when necessary. 

    The system worked very well until the CEO decided to move the company to India.

    Thanks for the input,


    MRM256

    Monday, August 28, 2017 9:39 PM