VB.net 运作Access数据表

vbs2019 + Access2019 。如果出现“Microsoft.ACE.OLEDB.12.0”时请参考上一篇文章。access数据表employee.accdb放在“Employee\Employee\bin\Debug”文件夹下是为了方便移植。由于试行时间较短，有些BUG没测试到，欢迎指出。
参考出处因时间太久找不到了。


所使用到的控件：
行号 txtRow.Text
ID txtID.Text
编号 txtStaffNum.Text
姓名 txtName.Text
性别 txtGender.Text
年龄 txtAge.Text
职务 txtDuties.Text

按键控件：
btmMoveFirst
btnPrevious
btnNext
btnMoveLast

btnAdd
btnUpdate
btnDel

其中：txtRow.Enabled=False

Imports System.Data.OleDb

Public Class Form1
    Dim ds As New DataSet()
    Dim intRowsIndex As Integer = 0 ''行号，与ID不一定相同，行号是固定的不可删除，从0计起
    ''ID可删除，删除后该号码就会被保留不会再被产生和使用
    Dim da As New OleDbDataAdapter()
    Dim conn As New OleDbConnection()

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ''CurDir.ToString : 把数据库文件放在了生成程序文件夹之下
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurDir.ToString & "\Employee.accdb;User Id=admin;Password=;"

        da.SelectCommand = New OleDbCommand("SELECT ID, 编号, 姓名,性别,年龄,职务 FROM Employee")
        ''ID是自动生成的编号，与职员的编号不是同一事物
        da.SelectCommand.Connection = conn

        ''用于更新
        da.UpdateCommand = New OleDbCommand("UPDATE Employee SET 编号 = @编号, 姓名 = @姓名 , 性别 = @性别, 年龄 = @年龄 , 职务 = @职务 WHERE ID = @ID")
        da.UpdateCommand.Connection = conn
        'da.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID")''ID不能被更新
        da.UpdateCommand.Parameters.Add("@编号", OleDbType.VarChar, 40, "编号")
        da.UpdateCommand.Parameters.Add("@姓名", OleDbType.VarChar, 40, "姓名")
        da.UpdateCommand.Parameters.Add("@性别", OleDbType.VarChar, 40, "性别")
        da.UpdateCommand.Parameters.Add("@年龄", OleDbType.Integer, 3, "年龄")
        da.UpdateCommand.Parameters.Add("@职务", OleDbType.VarChar, 40, "职务")

        ''用于增加
        da.InsertCommand = New OleDbCommand("INSERT INTO  Employee(编号, 姓名, 性别, 年龄, 职务) VALUES(@编号,@姓名,@性别,@年龄,@职务)")
        da.InsertCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID")
        da.InsertCommand.Parameters.Add("@编号", OleDbType.VarChar, 40, "编号")
        da.InsertCommand.Parameters.Add("@姓名", OleDbType.VarChar, 40, "姓名")
        da.InsertCommand.Parameters.Add("@性别", OleDbType.VarChar, 40, "性别")
        da.InsertCommand.Parameters.Add("@年龄", OleDbType.Integer, 3, "年龄")
        da.InsertCommand.Parameters.Add("@职务", OleDbType.VarChar, 40, "职务")

        ''用于删除
        da.DeleteCommand = New OleDbCommand("DELETE FROM Employee WHERE ID = @ID")
        da.DeleteCommand.Connection = conn
        da.DeleteCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID")

        da.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then '检查表是否为空
            FillFields()
        End If
        txtRow.Text = intRowsIndex + 1

    End Sub

    Private Sub FillFields()
        txtID.Text = ds.Tables(0).Rows(intRowsIndex).Item("ID").ToString()
        txtStaffNum.Text = ds.Tables(0).Rows(intRowsIndex).Item("编号").ToString()
        txtName.Text = ds.Tables(0).Rows(intRowsIndex).Item("姓名").ToString()
        txtGender.Text = ds.Tables(0).Rows(intRowsIndex).Item("性别").ToString()
        txtAge.Text = ds.Tables(0).Rows(intRowsIndex).Item("年龄").ToString()
        txtDuties.Text = ds.Tables(0).Rows(intRowsIndex).Item("职务").ToString()
    End Sub

    Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        Dim dr As DataRow
        Try
            dr = ds.Tables(0).NewRow()
            dr("ID") = txtID.Text
            dr("编号") = txtStaffNum.Text
            dr("姓名") = txtName.Text
            dr("性别") = txtGender.Text
            dr("年龄") = txtAge.Text
            dr("职务") = txtDuties.Text

            ds.Tables(0).Rows.Add(dr)
            da.Update(ds)
            ds.AcceptChanges()
            txtRow.Text = intRowsIndex + 1
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

        Dim dr As DataRow
        dr = ds.Tables(0).Rows(intRowsIndex)
        dr.BeginEdit()
        ' dr("ID") = txtID.Text  ''ID不能被更新
        dr("编号") = txtStaffNum.Text
        dr("姓名") = txtName.Text
        dr("性别") = txtGender.Text
        dr("年龄") = txtAge.Text
        dr("职务") = txtDuties.Text
        dr.EndEdit()

        da.Update(ds)
        ds.AcceptChanges()
        txtRow.Text = intRowsIndex + 1

    End Sub

    Private Sub btnDel_Click(sender As Object, e As EventArgs) Handles btnDel.Click
        Dim dr As DataRow
        dr = ds.Tables(0).Rows(intRowsIndex)
        dr.Delete()

        da.Update(ds)
        ds.AcceptChanges()
    End Sub

    Private Sub btmMoveFirst_Click(sender As Object, e As EventArgs) Handles btmMoveFirst.Click
        '返回到第0行，也就是第一行
        intRowsIndex = 0
        FillFields()
        txtRow.Text = intRowsIndex + 1
    End Sub

    Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click
        If intRowsIndex > 0 Then
            intRowsIndex -= intRowsIndex
            FillFields()
        Else
            MessageBox.Show("已经是第一条记录。")
        End If
        txtRow.Text = intRowsIndex + 1

    End Sub

    Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click
        If intRowsIndex < ds.Tables(0).Rows.Count - 1 Then
            intRowsIndex += intRowsIndex
            FillFields()
        Else
            MessageBox.Show("已经是最后一条记录了。")
        End If
        txtRow.Text = intRowsIndex + 1
    End Sub

    Private Sub btnMoveLast_Click(sender As Object, e As EventArgs) Handles btnMoveLast.Click
        intRowsIndex = ds.Tables(0).Rows.Count - 1  ''在ACCESS表中有效记录行之下还有一行（新建）空白行
        FillFields()
        txtRow.Text = intRowsIndex + 1
    End Sub
End Class