Imports System.Data.OleDb
 
Public Class Form2
 
    Dim column1string As String
    Dim column2string As String
    Dim column3string As String
    Dim column4string As String
    Dim Count As Integer
    Dim Count2 As Integer
    Dim Count3 As Integer
    Dim Count4 As Integer
    Dim playerquerysql As String
 
 
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        'create constant and things
        Dim con As New OleDb.OleDbConnection
        Dim cnn As New OleDb.OleDbConnection
        Dim ds_G As New DataSet
        Dim ds_Y As New DataSet
        Dim ds_T As New DataSet
        Dim dt_G As New DataTable
        Dim da_y As New OleDb.OleDbDataAdapter
        Dim da_g As New OleDb.OleDbDataAdapter
        Dim da_t As New OleDb.OleDbDataAdapter
        Dim sql As String
        Dim iStGreen As Integer
        Dim iStYellow As Integer
        Dim vGames(4) As String
        vGames(0) = ""
        vGames(1) = "Snap"
        vGames(2) = "Cribbage"
        vGames(3) = "Spilikins"
        vGames(4) = "Scrabble"
        Dim iGames As Integer
        Dim i As Integer
        Dim j As Integer
        Dim g As Integer
 
 
        iStYellow = 18
        iStGreen = 18
        iGames = 4
 
 
        Dim aGID As String = String.Concat(iStGreen, iGames)
        Dim aYId As String = String.Concat(iStYellow, iGames)
 
 
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = db.mdb"
 
        con.Open()
 
        sql = "SELECT * FROM Pupils"
        da_y = New OleDb.OleDbDataAdapter(sql, con)
        da_y.Fill(ds_Y, "Pupils")
 
        sql = "SELECT * FROM Pupils_1"
        da_g = New OleDb.OleDbDataAdapter(sql, con)
        da_g.Fill(ds_G, "Pupils_1")
 
        sql = "SELECT * FROM tmpFixtures"
        da_t = New OleDb.OleDbDataAdapter(sql, con)
        da_t.Fill(ds_T, "tmpFixtures")
 
        For g = 5 To 8
            For i = 0 To 17
                If ds_Y.Tables("Pupils").Rows(i).Item(g) <> True Then
                    For j = 0 To 17
 
                        If ds_G.Tables("Pupils_1").Rows(j).Item(g) <> True Then
                            If ds_Y.Tables("Pupils").Rows(i).Item("Class_Y") <> ds_G.Tables("Pupils_1").Rows(j).Item("Class_G") Then
                                If ds_Y.Tables("Pupils").Rows(i).Item("Oponent_Y").ToString <> ds_G.Tables("Pupils_1").Rows(j).Item("Forename_G") Then
 
                                    ds_G.Tables("Pupils_1").Rows(j).Item(g) = True
                                    ds_Y.Tables("Pupils").Rows(i).Item(g) = True
                                    ds_Y.Tables("Pupils").Rows(i).Item("Oponent_Y") = ds_G.Tables("Pupils_1").Rows(j).Item("Forename_G")
                                    ds_G.Tables("Pupils_1").Rows(j).Item("Oponent_G") = ds_Y.Tables("Pupils").Rows(i).Item("Forename_Y")
 
 
                                    Dim cb_G As New OleDb.OleDbCommandBuilder(da_g)
                                    Dim updateCommand As New OleDbCommand("UPDATE abc SET Forename_G = ?, Class_G = ?", con)
 
                                    updateCommand.Parameters.Add("Forename_G", OleDbType.VarChar, 0, "Forename_G")
                                    updateCommand.Parameters.Add("Class_G", OleDbType.VarChar, 0, "Forename_G")
 
 
                                    da_g.UpdateCommand = updateCommand
 
 
                                    da_t.Update(dt_G)
                                    da_t.Update(dt_T, "tmpFixtures") 'error
 
                                    updateCommand.Parameters.Add("c", OleDbType.Integer, 0, "c")
                                    updateCommand.Parameters.Add("c", OleDbType.VarChar, 0, "c")
 
                                    da_g.Update(ds_Y, "pupils")
 
 
                                    Exit For
                                End If
                            End If
                        End If
 
                    Next j
                    Exit For
                End If
 
            Next i
            Exit For
        Next g
 
        con.Close()
    End Sub

Dim OLECon As New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = db.mdb")
Dim OLECmd As New OleDb.OleDbCommand
Dim OLEStr as String
 
OLEStr ="UPDATE abc SET" 
OLEStr += " Forname_G = '" & ds_G.Tables("Pupils_1").Rows(j).Item("Forename_G") & "',"
OLEStr += " Class_G = '" & ds_G.Tables("Pupils_1").Rows(j).Item("Class_G") & "'"
 
OLECmd = New OleDb.OleDbCommand(OLEStr, OLEConn)
 
OLECon.Open()
OLECmd.ExecuteNonQuery()
OLECon.Close()