' Imports is not required, but make the syntax reasier to read
Imports System.Data.oledb

Public Class Form1
    Inherits System.Windows.Forms.Form

    Dim da As New OleDbDataAdapter
    Dim dt As New DataTable

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' The connection object is now OleDbConnection
        Dim con As New OleDbConnection()
        Dim cmd As New OleDbCommand()
        Dim dr As OleDbDataReader

        ' Can't put the connection string as argument to open method
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\Test.mdb"
        Try
            con.Open()
        Catch ex As Exception
            MsgBox("Yikes! Can't open the database" & vbCr & ex.Message, MsgBoxStyle.Critical, "Error!")
            Exit Sub
        End Try

        ' Create a DataReader object.  You use the "ligthweight" data reader
        ' object for doing things like populating a combobox.  The data reader
        ' is not well suited for doing much else.
        cmd.Connection = con
        cmd.CommandText = "Select * from Table1"
        dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

        ' Fill a combo box with the datareader
        Do While dr.Read = True
            ' the GetString() method assumes you already know the
            ' schema of the table, and that the first element is a string
            ComboBox1.Items.Add(dr.GetString(0))
        Loop
        ' The close to the DataReader is required, because the DataAdapter
        ' below internally uses a datareader object... and you can only have
        ' one datareader object open at a time. 
        dr.Close()

        ' Use a DataAdapter to fill a DataTable.  This is the "heavyweight"
        ' method used to fill either tables or the new "dataset" object.
        ' There is an implied con.open() and con.close() within the Fill
        ' method itself... so the data adapter is normally only connected
        ' to the database as long as it takes to do the fill()/update()
        da.SelectCommand = cmd
        da.Fill(dt)

        ' Bind the datagrid with the new dataset
        DataGrid1.DataSource = dt

        ' Since we explicitly opened the Connection object, it remains open
        ' after the Fill method.  We could have let the DataAdapter do the
        ' open and close for us, but I like to work the error messages up front.
        con.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dt_changes As DataTable
        Dim cb As OleDbCommandBuilder
        Dim rows As Integer

        ' Let's see if anybody made any changes to the table via the datagrid
        dt_changes = dt.GetChanges()

        If Not IsNothing(dt_changes) Then
            Try
                ' Create the SQL insert/delete/update commands that will be 
                ' used by the data adapter's update method below.  This is
                ' optional... you *could* populate the 3 properties of the
                ' data adapter yourself (but why?) You might also noice that
                ' the cb object is not reference anywhere... it's magic!
                cb = New OleDbCommandBuilder(da)
                ' Update the changes to the database.  Remember, You can only
                ' update a table that has a primary key!
                rows = da.Update(dt_changes)
                MsgBox(rows & " updated")
            Catch ex As Exception
                MsgBox("Yikes, Can't save the changes!" & vbCrLf & ex.Message, MsgBoxStyle.Exclamation)
            End Try
        Else
            MsgBox("Hey, there's nothing to udpate")
        End If

        ' No need to close any database connections... it's already closed
        ' because this time we let the DataAdapter do the open/close for us
    End Sub
End Class

