I have a DataTable that uses an inner join to two database tables. I did this to overcome the ACCESS limitation of 99 columns. (Oledb).

I have generated two routines that have the Insert/Delete/Update commands (one is shown below) and they both work fine… by themselves. When the either of the routines is executed, the database is Updated/Inserted/Deleted fine, but when I go to update the other table, there is nothing to update. It is like the DataAdapter for the DataTable was cleared of all changes. I have tried the Transaction Class objects on the Connection (they are in the code below), but that does not keep the DataTable from being cleared of changes. I am pretty sure this is the wrong approach.

Question: Is there a way to get an instance of the DataTable and use that to update the Database for one of the database tables and then get another instance of the DataTable to update the other database table? …Or am totally out in the weeds again?

Thanks for your help.

Code:
        Using connection As OleDb.OleDbConnection = New OleDb.OleDbConnection(StrCon)
            connection.Open()

            Dim dt As DataTable = Me.ScoreboardDataSet.tbl_MatchData
            Dim dataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
            Dim parameter As OleDb.OleDbParameter

            Dim myTrans As OleDb.OleDbTransaction = connection.BeginTransaction()
            Dim command As OleDb.OleDbCommand = connection.CreateCommand()

            'Generate Insertcommand statement for tbl_GreenMatchData
            command = New OleDb.OleDbCommand("INSERT INTO tbl_GreenMatchData (GameIndex) " & "VALUES (@GameIndex)", connection)
            command.Transaction = myTrans

            command.Parameters.Add("@GameIndex", OleDb.OleDbType.Integer, 3, "GameIndex")
            command.Parameters.Add("@MatchNumber", OleDb.OleDbType.Integer, 3, "MatchNumber")
            dataAdapter.InsertCommand = command

            ' Create the DeleteCommand for tbl_GreenMatchData
            command = New OleDb.OleDbCommand("DELETE * FROM tbl_GreenMatchData WHERE MatchNumber = @MatchNumber", connection)
            command.Transaction = myTrans

            parameter = command.Parameters.Add("MatchNumber", OleDb.OleDbType.Integer, 3, "@MatchNumber")
            parameter.SourceVersion = DataRowVersion.Original
            dataAdapter.DeleteCommand = command

            'Generate the UpdateCommend for tbl_GreenMatchData
            Dim updateSQL As String = "UPDATE tbl_GreenMatchData SET " & _
            "GameIndex = @GameINdex, " & _
            "GTD1 = @GTD1, " & _
            "GTD2 = @GTD2 " & _
            "MatchScoreLocked = @MatchScoreLocked WHERE MatchNumber = @MatchNumber"

            command = New OleDb.OleDbCommand(updateSQL, connection)
            command.Transaction = myTrans

            command.Parameters.Add("@GameIndex", OleDb.OleDbType.Integer, 3, "GameIndex")
            command.Parameters.Add("@GTD1", OleDb.OleDbType.Integer, 3, "GTD1")
            command.Parameters.Add("@GTD2", OleDb.OleDbType.Integer, 3, "GTD2")

            parameter = command.Parameters.Add("@MatchNumber", OleDb.OleDbType.Integer, 3, "MatchNumber")
            parameter.SourceVersion = DataRowVersion.Current
            dataAdapter.UpdateCommand = command

            'Update Tbl_MatchData in database

            dataAdapter.Update(dt)

            myTrans.Commit()
            connection.Close()

        End Using