VB.NET 使用 OleDb 操作 Access 数据库

这里采用 OleDb 方式对 Access 数据库进行访问，主要内容都来自 MSDN 帮助文档。建议新手注意 MSDN 的用途。下面的代码只是示例，请自行组织代码结构。

首先引用 

导入命名空间 ：Imports System.Data.OleDb

然后我们要用到以下几个类，OleDbConnection，OleDbDataAdapter，  OleDbCommand ， OleDbCommandBuilder。详细用法请参照 MSDN

现在定义以下全局变量：

' 定义相关变量' 到数据源的连接。并实例化Public odConnection As OleDbConnection = New OleDbConnection()' 存储读取当前数据表的sql语句,写入数据库时必须使用Public sql_select As String = "Select * From "' 连接字符串前面一部分，不包括文件名Public cnnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data source="Public openFileDialog As OpenFileDialog = New OpenFileDialog()

' 主测试函数，需要一个 ComboBox1 和 DataGridView1。Private Sub TestMain()' 弹出选择文件对话框选择数据库文件With openFileDialog.Title = "Open DB File".Filter = "Aaccess07-10 Database|*.accdb|Access97-03 Database|*.mdb|All Files|*.*".FileName = vbNullString.ShowDialog()End WithTry' 由数据库文件名连接数据库，并打开连接If odConnection Is Nothing ThenodConnection = New OleDbConnection(cnnStr + openFileDialog.FileName)End IfodConnection.ConnectionString = cnnStr + openFileDialog.FileNameodConnection.Open()Catch ex As ExceptionodConnection = NothingMsgBox(ex.Message)End TryDim sql As String = "Select * From "Dim tns As List(Of String)' 获得表名列表Dim num As Integer = GetTableNames(tns, odConnection)' 将表名列表添加到 ComboBox ComboBox1.Items.Clear()ComboBox1.Items.AddRange(tns.ToArray)' 根据表名列表的第一个表名获取数据表数据，显示在 DataGridView 中sql += tns.Item(0)' 存储该 sql 语句到 sql_select 读取其他表时，须更新该语句sql_select = sql' 绑定结果 DataTable 到 DataGridViewDataGridView1.DataSource = ReadDataFromDB(sql_select, odConnection)End Sub ' 根据 odConnection 获得数据库中表的个数及各个表名（按顺序）' 各个表名存储在输入的参数 tableNames 中，表的个数作为返回值' 输入参数 tableNames 可以为空，可将其 显示在 ComboBox 中。Function GetTableNames(ByRef tableNames As List(Of String), ByVal odConnection As OleDbConnection) As IntegerIf tableNames Is Nothing ThentableNames = New List(Of String)End IftableNames.Clear()If odConnection Is Nothing ThenMsgBox("数据库未连接")Return 0End If' 获取数据库中数据表的名称及个数' Type 有："TABLE,VIEW,ACCESS TABLE,SYSTEM TABLE", Dim dt As DataTableDim tableNum As IntegerDim i As IntegerTrydt = odConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _New Object() {Nothing, Nothing, Nothing, "TABLE"})Catch ex As ExceptionMsgBox(ex.Message)Return 0End TrytableNum = dt.Rows.CountIf tableNum = 0 ThentableNames.Clear()Return 0End IfFor i = 0 To tableNum - 1tableNames.Add(dt.Rows(i).Item("TABLE_NAME"))NextReturn tableNumEnd Function
' 根据表名将数据表数据读取到 DataTable 并返回' sql 为当前表读取时的 Select 语句' odConnection 为当前数据库的连接 Private Function ReadDataFromDB(ByVal sql As String, ByVal odConnection As OleDbConnection) As DataTableIf (Sql Is Nothing Or Sql Is vbNullString Or odConnection Is Nothing) ThenReturn NothingEnd IfDim dt As DataTable = New DataTableTryDim odAdapter As OleDbDataAdapter = New OleDbDataAdapter(Sql, odConnection)' 填充数据到 DataTableodAdapter.Fill(dt)Catch ex As ExceptionMsgBox(ex.Message)Return NothingEnd TryReturn dtEnd Function
' 当在 DataGridView 更改数据时，将更改写入数据库' sql 为当前表读取时的 Select 语句' odConnection 为当前数据库的连接Private Function WriteDataToDB(ByVal sql As String, ByVal odConnection As OleDbConnection) As IntegerIf (sql Is Nothing Or sql Is vbNullString Or odConnection Is Nothing) ThenReturn NothingEnd IfDim num As IntegerTry' odAdapter 数据命令和一个数据库连接，于填充 DataSet 和更新数据源Dim odAdapter As OleDbDataAdapter = New OleDbDataAdapter(sql, odConnection)' odCmdBuilder 自动生成用于协调对 DataSet 的更改与关联数据库的单表命令Dim odCmdBuilder As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(odAdapter)odCmdBuilder.QuotePrefix = "["odCmdBuilder.QuoteSuffix = "]"' 获取在数据源中执行插入操作所需的自动生成的 OleDbCommand 对象odCmdBuilder.GetInsertCommand()' 获取在数据源中执行删除操作所需的自动生成的 OleDbCommand 对象odCmdBuilder.GetDeleteCommand()' 获取在数据源中执行更新操作所需的自动生成的 OleDbCommand 对象odCmdBuilder.GetUpdateCommand()num = odAdapter.Update(DataGridView1.DataSource) ' 就保存更改，在实质上是把网格中的数据回发给数据库 Return numCatch ex As ExceptionMsgBox(ex.Message)Return 0End TryEnd Function
' 可在 ComboBox 的 SelectedIndexChanged 消息函数中更改 DataGridView 的显示不同的表Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChangedDim sql As String = "Select * From " + ComboBox1.TextDataGridView1.DataSource = ReadDataFromDB(sql, odConnection)End Sub