vb.net 在 Visual Studio 2013 中使用 Access 数据库

Working with Access databases in Visual Studio 2013

提问by HotelCalifornia

I've been trying to figure out a way to use Visual Basic to work with an Access database, but all of the libraries (ADODB, etc.) that I've seen referenced on the internet either don't exist in VS2013 or don't have all the features that I'd like to use, like Recordset objects (OleDb is one such library). Is this just a case of 'you need to install the correct library'? Or am I missing some new standard with working with Microsoft databases?

我一直试图找出一种使用 Visual Basic 来处理 Access 数据库的方法，但是我在互联网上看到的所有库（ADODB 等）要么不存在于 VS2013 中，要么不存在没有我想要使用的所有功能，例如 Recordset 对象（OleDb 就是这样的一个库）。这只是“您需要安装正确的库”的一种情况吗？或者我是否缺少使用 Microsoft 数据库的一些新标准？

回答by HotelCalifornia

It turns out I was, in fact, using the wrong classes. The page here(thanks @Plutonix) makes references to the DataSet11 and OleDbAdapter1 objects, which when used in conjunction, appear to have the sort of functionality I saw in the old DAO and ADO Recordset objects.

事实证明，我实际上使用了错误的类。这里的页面（感谢@Plutonix）引用了 DataSet11 和 OleDbAdapter1 对象，当它们结合使用时，似乎具有我在旧的 DAO 和 ADO Recordset 对象中看到的那种功能。

回答by Bobort

I have an ASP.NET website using VB.NET an a simple Microsoft Access 2000 database backend. I actually wrote my own custom classes to get certain tables and queries and data from the database, and it is based on OleDb. It's not the finest code available, and it is a bit shameful, actually. But it gets the job done for the simple application I'm using it for.

我有一个使用 VB.NET 的 ASP.NET 网站，一个简单的 Microsoft Access 2000 数据库后端。我实际上编写了自己的自定义类来从数据库中获取某些表、查询和数据，它基于 OleDb。这不是最好的代码，实际上有点可耻。但它完成了我使用它的简单应用程序的工作。

Cut version of file AccessDatabase.vb

文件 AccessDatabase.vb 的剪切版本

Imports Microsoft.VisualBasic
Imports System.Data.OleDb

Public Class AccessDatabase
    Friend db As New OleDbConnection
    Private sPath As String

    Public Sub New(ByRef sPath As String)
        GetDatabase(sPath)
    End Sub

    'Use Server.MapPath("App_Data\WebContent.mdb") to load the database.
    Private Function GetDatabase(ByRef sPath As String) As OleDbConnection
        Try
            If db.State <> System.Data.ConnectionState.Open Then
                db = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath)
                db.Open()
            End If
        Catch e As Exception
            Throw New Exception("Exception encountered when attempting to open database " & sPath, e)
        End Try
        Return db
    End Function

    Public Function GetTable(ByRef sTableName As String, Optional ByRef sWhere As String = "", Optional ByRef sSort As String = "") As AccessData
        Dim a As New AccessData(Me)
        a.SetTable(sTableName, sWhere, sSort)
        Return a
    End Function

    Public Sub Close()
        If db.State <> Data.ConnectionState.Closed Then
            db.Close()
        End If
    End Sub

    Protected Overrides Sub Finalize()
        Try
            If Not db Is Nothing Then
                If db.State <> Data.ConnectionState.Closed Then
                    db.Close()
                End If
            End If
        Catch ex As Exception

        End Try
    End Sub
End Class

Cut version of File AccessData.vb

File AccessData.vb 的删减版

Imports Microsoft.VisualBasic
Imports System.Data.OleDb

Public Class AccessData

    Private db As AccessDatabase
    Private data As OleDbDataReader

    Public Sub New(ByRef d As AccessDatabase)
        SetDB(d)
    End Sub

    Public Sub New(ByRef d As AccessDatabase, ByRef sTableName As String, Optional ByRef sWhere As String = "", Optional ByRef sSort As String = "")
        SetDB(d)
        SetTable(sTableName, sWhere, sSort)
    End Sub

    Public Sub SetDB(ByRef d As AccessDatabase)
        db = d
    End Sub

    Public Sub SetTable(ByRef sTableName As String, Optional ByRef sWhere As String = "", Optional ByRef sSort As String = "")
        If sWhere = "" And sSort = "" Then
            SetFromQuery("SELECT " & sTableName & ".* FROM " & sTableName)
        ElseIf sSort = "" Then
            SetFromQuery("SELECT " & sTableName & ".* FROM " & sTableName & " WHERE " & sWhere)
        ElseIf sWhere = "" Then
            SetFromQuery("SELECT " & sTableName & ".* FROM " & sTableName & " ORDER BY " & sSort)
        Else
            SetFromQuery("SELECT " & sTableName & ".* FROM " & sTableName & " WHERE " & sWhere & " ORDER BY " & sSort)
        End If
    End Sub

    Public Sub SetFromQuery(ByRef sQuery As String)
        Dim c As OleDbCommand
        c = New OleDbCommand(sQuery, db.db)
        data = c.ExecuteReader()
    End Sub

    'Returns the value of the requested field of the current row of the reader
    Public Function GetValue(ByRef sField As String) As String
        Dim iOrdinal As Integer

        Try
            iOrdinal = data.GetOrdinal(sField)
            If Not data.GetValue(iOrdinal).Equals(DBNull.Value) Then
                Return data.GetValue(iOrdinal).ToString()
            End If
        Catch e As System.IndexOutOfRangeException
            Throw New System.IndexOutOfRangeException("Field '" & sField & "' was requested from " & vbCrLf & sQuery & "," & vbCrLf & "but it does not exist.", e)
        Catch e As System.InvalidOperationException
            Throw New System.InvalidOperationException("No data exists for the current row in field '" & sField & "'.  Make sure you have performed a Read() operation and that you are not at the EOF or BOF of the data stream.", e)
        End Try

        Return ""
    End Function

    'This will close the stream when data.Read() returns false
    Public Function Read() As Boolean
        Dim bResult As Boolean
        bResult = data.Read()
        If Not bResult Then
            data.Close()
        End If
        Return bResult
    End Function
End Class