Working with Access databases in Visual Studio 2013

Question

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?

Answer 1

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.

Answer 2

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.

Cut version of file 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

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