Unlocking Microsoft Access Data with ADO.NET

Article
09/12/2006

Paul Cornell
Microsoft Corporation

December 6, 2001

Microsoft® Office provides several features and tools for storing and managing data, such as Data Access Objects (DAO), ActiveX® Data Objects (ADO), Microsoft Word mail merges, Microsoft Excel Web queries, Microsoft Query, data access pages, Microsoft Access data projects, Office data connections, Office data links, and so on. The Microsoft .NET platform provides additional data access features and tools, such as ADO.NET, .NET data connections, and database projects.

In this month's column, I introduce you to unlocking and managing your Microsoft Access data using Microsoft Visual Studio® .NET, Microsoft Visual Basic® .NET, and most importantly, ADO.NET.

Determining When ADO.NET Is the Best Choice

Given that Office provides a wide array of data access features and tools, why not stick with Office to manage its own data? Granted, in many cases, you will want to use the built-in Office features and tools appropriate to a particular data task. However, ADO.NET provides a uniform method of adding data connections and coding data solutions, unlike the multitude of data entry points and data access libraries in Office.

While you can still call ADO functionality in Visual Studio .NET and Visual Basic .NET if you choose, in many respects, ADO.NET is a better data access solution than ADO for the following reasons:

ADO.NET allows multiple tables to be included in a dataset, including the relationships between those tables. ADO only allows for one result table (although this single result table may be the result of a JOIN action performed on multiple related tables).
ADO.NET provides disconnected access to data. ADO can provide disconnected data access, but ADO is designed primarily for connected data access.
ADO.NET provides a record navigation paradigm that allows you to access data out of sequence (unlike the sequential ADO Move methods) and traverse among data tables using their relationships.
Because ADO.NET transmits data using XML, you can provide richer data types than ADO, obtain better data access performance, and allow data to pass through firewalls.
ADO.NET is highly optimized for working with Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 databases (as reflected in the ADO.NET Sql objects), as well as providing programmatic access to other OLE DB data sources such as Microsoft Access databases (as reflected in the ADO.NET OleDb objects).

And of course, if you want to take advantage of many of the new features in .NET, you should use the .NET data access features and tools (see my earlier column, Introducing .NET to Office Developers, for more information about the .NET platform).

Getting Started with ADO.NET

Figure 1 shows the main objects that you will use when you are getting started with ADO.NET.

Figure 1. Main objects in the ADO.NET object model (source: Inside .NET Managed Providers)

These objects include:

The OleDbConnection and SqlConnection objects, which represent connections to databases, and are similar to an ADO Connection object.
The OleDbCommand and SqlDbCommand objects, which represent strings of SQL text to be issued directly to the database, and are similar to an ADO Command object.
The OleDbDataAdapter and SqlDataAdapter objects, which hold database SELECT, INSERT, UPDATE, and DELETE commands. These objects serve as intermediaries between databases and DataSet objects. You use the Fill method to fill a DataSet object and disconnect from a database, and you use the Update method to reconnect to a database and save the changes made in the DataSet object back to a database.
The DataSet object contains a copy of the actual data, similar to a disconnected ADO Recordset object. A DataSet object can contain DataRelation and Contraint objects, representing relationships between data tables and constraints on the data in those tables, respectively. A DataSet object can also contain DataTable objects, which in turn can contain DataColumn and DataRow objects. A dataset can map tables and columns in a database to their respective DataTable and DataColumn objects by using DataTableMapping and DataColumnMapping objects.
The SqlDataReader and OleDbReader objects represent connected, forward-only, read-only, data readers. This is useful if you can maintain a constant connection to a database and you just want to read data and not change it, in which case you do not have to create a DataSet object. A data reader is similar to a connected, forward-only, read-only ADO Recordset object.

For reference, in Office Visual Basic for Applications (VBA) using ADO, here is one way that you could open the Northwind sample Access database, copy data from the Products table into a Recordset object, and report on some of the data in the recordset's first data record:

' Office VBA and ADO code - ADOCode.bas.
Public Sub ADOExample()

    ' Set a reference to the ADO library first.
    Dim objConn As ADODB.Connection
    Dim objRS As ADODB.Recordset
    
    Set objConn = New ADODB.Connection
    
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "User ID=Admin;" & _
        "Data Source=C:\Program Files\Microsoft " & _
        "Office\Office10\Samples\Northwind.mdb"
    objConn.Open
    
    Set objRS = objConn.Execute("SELECT * FROM Products")
    
    objRS.MoveFirst
    
    MsgBox Prompt:=objRS.Fields("ProductName").Value & ", " & _
        objRS.Fields("UnitsInStock").Value
        
    objRS.Close
    objConn.Close

End Sub

This ADO code is straightforward if you have coded in ADO before:

ADO Connection and Recordset object variables are declared, which reference a connection to a database and the database's underlying data, respectively.
The Connection object's ConnectionString property is set to a database-specific String value that represents information needed to connection to a database (the Northwind database in this example).
The Connection object's Open method establishes the actual connection to the database.
The Connection object's Execute method retrieves data from the database and places the retrieved data into the Recordset object.
The Recordset object's MoveFirst method moves to the first record in the recordset.
The Recordset object's Fields property references a particular column in the record, and the Field object's Value property retrieves the actual data in the field.

As comparison, here is some Visual Basic .NET sample code for a console application that basically performs the same actions using ADO.NET; however, instead of putting data into a recordset, an ADO.NET data reader stays connected to the data:

' Visual Basic .NET and ADO.NET code - ADONETCode.vb.
' Using connected ADO.NET data access.
' Console application.
Imports System.Data.OleDb

Module Module1

    Sub Main()

        ' Set a reference to System.Data.dll first. 
        Dim objConn As New OleDbConnection _
            ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "User ID=Admin;" & _
            "Data Source=C:\Program Files\Microsoft" & _
            "Office\Office10\Samples\Northwind.mdb")

        objConn.Open()

        Dim objCmd As New OleDbCommand _
            ("SELECT * FROM Products", objConn)
        Dim objDataReader As OleDbDataReader = objCmd.ExecuteReader

        objDataReader.Read()
        Console.Write(objDataReader.Item("ProductName") & ", " & _
            objDataReader.Item("UnitsInStock"))

    End Sub

End Module

Here is how the code works:

The code Imports System.Data.OleDb helps cut down the number of keystrokes required to access an object or an object's members. For example, while you could code ADODB.Recordset in ADO, you generally do not because you have referenced the ADODB library. In the same way, while you could code System.Data.OleDb.OleDbConnection in ADO.NET, you do not have to if you have already imported the OleDbConnection object's associated namespace, System.Data.OleDb (provided we have set a reference to System.Data.dll first).
In ADO, we had to first declare an object of type Connection, and then use the ConnectionString property to define connection information. In .NET, we use the concepts of parameterized constructors and initializers to declare an object of type OleDbConnection and define connection information in just one line of code.
Similar to ADO, we use the OleDbConnection object's Open method in ADO.NET to establish the actual connection to the database.
Next, we declare an ADO.NET OleDbCommand object to store the command to retrieve data from the database.
We then declare an ADO.NET OleDbDataReader object to read the data from the database.
Using the OleDbCommand object's ExecuteReader method, the code allows the data reader to access specific data in the database.
The OleDbDataReader object's Read method reads the first record in the database.
The Console.Write method writes data from specific columns (using the OleDbReader object's Item property) to the Command window.

As an alternative, here is some Visual Basic .NET sample code that performs the same actions as the previous ADO.NET sample, but in this case, the data is copied to an ADO.NET dataset and then the connection to the database is dropped:

' Visual Basic .NET and ADO.NET code - ADONETCode.vb.
' Using disconnected ADO.NET data access.
' Console application.
Imports System.Data.OleDb

Module Module1

    Sub Main()

        ' Set a reference to System.Data.dll first. 
        Dim objConn As New OleDbConnection _
            ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "User ID=Admin;" & _
            "Data Source=C:\Program Files\Microsoft" & _
            "Office\Office10\Samples\Northwind.mdb")

        objConn.Open()

        Dim objAdapter As New OleDbDataAdapter _
            ("SELECT * FROM Products", objConn)
        Dim objDataSet As New DataSet()

        objAdapter.Fill(objDataSet)

        With objDataSet.Tables("Table").Rows(0)

            Console.Write(.Item("ProductName") & ", " _
                & .Item("UnitsInStock"))

        End With

    End Sub

End Module

Here is how this code differs from the code in the previous ADO.NET sample:

The OleDbAdapter object is similar to the OleDbDataReader object in that it accesses data from a database, but the OleDbAdapter object's Fill method is used to copy the data tables and relationships from the database into the DataSet object.
The DataSet object's Tables property accesses all of the dataset's tables, while the Rows property accesses individual rows in a given table in the dataset.

Can I Use ADO.NET in Office VBA?

Although you may want to use the new features of ADO.NET in conjunction with Office VBA, currently you cannot do so. This is because if you run RegAsm.exe (the Assembly Registration Tool in the .NET Frameworks SDK) to register System.Data.dll with COM (on which Office VBA is based), and then try to reference the generated type library file from Office, you will get no exposed members in the Office Visual Basic Object Browser. This is because COM fails to recognize core private ADO.NET members and parameterized constructors. So, for now at least, you should continue to use ADO in Office VBA.

Using the Visual Studio .NET Data Access Tools

Visual Studio .NET provides many convenient tools to access Office data, including the creation of reusable data connections, database projects, and the Data Form Wizard.

Data Connections

Data connections allow you to define database connectivity details once, and then reuse this information in several solutions. Here is an example of how to create a reusable data connection in Visual Studio .NET:

On the Tools menu, click Connect to Database. The Data Link Properties dialog box appears.
On the Provider tab, click the appropriate data provider, and then click Next. For example, to connect to the Northwind sample database in Access, click Microsoft Jet 4.0 OLE DB Provider.
Fill in the information on the Connection tab specific to the selected data provider. For example, for the Northwind sample database, click the ... button next to the Select or enter a database name box, browse to Northwind.mdb, and click Open.
Click Test Connection. If the connection succeeds, click OK. If not, recheck the information on the Connection tab.
Click OK to close the Data Link Properties dialog box.

You now have a reusable data connection that can be added to your Visual Studio .NET projects.

Building on these steps, here is an example of how to create ADO.NET objects that interoperate with existing data connections in the Server Explorer window:

In the Designer view of a Windows Application project, on the View menu, click Server Explorer, expand the desired data connection, and drag a table, view, or stored procedure onto the design surface. Visual Studio .NET creates an OleDbConnection/SqlConnection object (depending on the type of database), an OleDbDataAdapter/SqlDataAdapter object, and four OleDbCommand/SqlCommand objects for selecting, inserting, updating, and deleting data.
Right-click the OleDbDataAdapter/SqlDataAdapter object and click Generate Dataset. In this case, select New, leave the default setting of DataSet1, check the tables you want to add to the dataset, check Add this dataset to the designer, and click OK. Visual Studio .NET creates a DataSet object.
Right-click the OleDbDataAdapter/SqlDataAdapter object and click Preview Data, click Fill Dataset, and then click Close.

Note In Visual Studio .NET Beta 2, the Fill Dataset button does not appear to generate the code to actually fill the Dataset object. You may need to add code manually, such as the following, to the Form_Load or Page_Load event: Me.OleDbDataAdapter1.Fill(DataSetName11).

Here is an example of how to add a data grid on a Windows Form, bound to an existing dataset generated in the previous example:

In Designer view, on the Toolbox, click the Windows Forms tab, and then double-click DataGrid.
In the Properties window for DataGrid1, select DataSet11 for the DataSource property, and then select the table for the DataMember property.
Run the application.
If no data appears in DataGrid1, add code similar to the following, in the Form_Load event: Me.OleDbDataAdapter1.Fill(DataSetName11).

Tip To create ADO.NET objects without existing data connections in the Server Explorer window:
1. In design view, on the Toolbox, click the Data tab (this will not work in Code view).
2. Drag an ADO.NET object onto the design surface.
3. Right-click the ADO.NET object and click Properties.
4. Fill in the appropriate properties for the selected ADO.NET object.

Database Projects

Visual Studio .NET database projects allow you to quickly create solutions that are database oriented. Here is an example of how to create a database project in Visual Studio .NET:

On the File menu, point to New, and click Project.
In the Project Types pane, expand Other Projects, and click Database Projects.
In the Templates pane, click Database Project.
Fill in the Name and Location boxes, and click OK.
Click the Add to Solution or Close Solution button to add this project to an existing solution or create a standalone solution, respectively.
In the Add Database Reference dialog box, select an existing data connection, or click Add New Reference to add a new data connection. If you click Add New Reference, follow the steps in the previous section to complete the Data Link Properties dialog box (if you add a new data connection using this method, the data connection will appear in the Server Explorer window).

Data Form Wizard

The Visual Studio .NET Data Form Wizard allows you to quickly connect to a database and build a simple user interface for displaying and interacting with data. To use the Data Form Wizard:

In a Windows Application project, on the Project menu, click Add Windows Form.
In the Templates pane, click Data Form Wizard, click Open, and then click Next.
Type DataSet1 in the Create a new dataset named box, and click Next.
Select an existing data connection or create a new data connection, and click Next.
Select the items you want DataSet1 to be filled with, and click Next.
If you selected more than one item, define the relationships between the items, and click Next.
Define the tables and columns you want to display on the form, and click Next.
Select the display style options, and click Finish.

Note Be sure to set the new data form as the startup form (on the Project menu, click Properties. Expand the Common Properties folder, click General, select the data form in the Startup object list, and then click OK).

Note Be sure to bind the data in DataSet1 to the data form by inserting the following code into the data form's Load event: Me.OleDbDataAdapter1.Fill(objDataSet1)
Run the application (on the Debug menu, click Run).

Extended ADO.NET Code Examples

To show you some more ADO.NET code, let's finish this month's column with a couple of extended code examples I created in ADO.NET.

Accessing Data in Read-Only, Forward-Only Format Using the DataReader Object

Many times, all you want to do is stream out data—you do not want to travel back and forth through the data, and you do not want to change the data (we cordially refer to this as firehose data). The ADO.NET DataReader object is designed specifically for this purpose. Here is a code example I wrote that iterates through all of the selected data in a connected database:

' Visual Basic .NET code.
' Console application.
' References:
'   System
'   System.Data
'   System.XML

Imports System.Data.OleDb                   ' For OleDb objects.
Imports Microsoft.VisualBasic.ControlChars  ' For CrLf constant.

Module Module1

    Sub Main()

        ' Create and initialize OleDbConnection, OleDbCommand,
        ' and OleDbDataReader objects.
        Dim objConn As New _
            OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "User ID=Admin;" & _
            "Data Source=C:\Program Files\Microsoft" & _
            "Office\Office10\Samples\Northwind.mdb")

        objConn.Open()

        ' Execute the command and attach the data reader to the
        ' selected data.
        Dim objCmd As New OleDbCommand("SELECT * FROM Products", _
            objConn)
        Dim objReader As OleDbDataReader = objCmd.ExecuteReader

        ' Read the data and list values. 
        Call ReadData(objReader)

    End Sub

    Public Sub ReadData(ByVal objReader As OleDbDataReader)

        ' Purpose: Lists out the data values for a given data reader. 
        ' Accepts: objReader - The data reader. 

        Dim intField As Integer             ' Current field in row.
        Dim intColumn As Integer            ' Current column name.
        Dim blnColumns As Boolean = False   ' Whether column names have
                                            ' been listed.

        With objReader

            ' Read one row at a time until end-of-file is reached.
            Do While .Read = True

                For intField = 0 To .FieldCount - 1

                    ' List the column names first. 
                    If blnColumns = False Then

                        For intColumn = 0 To .FieldCount - 1

                            If intColumn = .FieldCount - 1 Then
                                Console.Write(.GetName(intColumn) & _
                                    CrLf)
                            Else
                                Console.Write(.GetName(intColumn) & _
                                    ", ")
                            End If

                        Next intColumn

                        ' Only list column names once. 
                        blnColumns = True

                    End If

                    ' List the value of each field in the current row.
                    If intField = .FieldCount - 1 Then
                        Console.Write(.Item(intField) & CrLf)
                    Else
                        Console.Write(.Item(intField) & ", ")
                    End If

                Next intField

            Loop

        End With

        ' Pause so user can see the data in the console window. 
        Console.Write("Press any key to continue...")
        Console.Read()

    End Sub

End Module

Here is how the code works:

As you saw in an earlier code example, the Imports code (such as Imports System.Data.OleDb) helps cut down the number of keystrokes required to access an object or its members. Similarly, OleDbConnection, OleDbCommand, and OleDbDataReader objects are declared and initialized to represent the database connection, data records, and record cursor, respectively. The real heart of this code is in the ReadData subroutine.
The Do loop uses the OleDbDataReader object's Read method to read one row of data at a time until the method returns False (which means there is no more data left to read).
The OleDbDataReader object's FieldCount property returns the number of data fields (columns) in the row of data. If this is the first row of data, the OleDbDataReader object's GetName property is called on column to return the column's name.
The OleDbDataReader object's Item property uses an index number in conjunction with the FieldCount property to return the value of each data field in the data row.

Working with Datasets Using the DataAdapter, DataSet, DataTable, DataRow, and DataColumn Objects

The ADO.NET DataSet object is designed to work with disconnected data. You can mimic the structure and data of an entire database—tables, rows, columns, fields, even relationships—in a dataset. When you are finished working with the data, you can synchronize the data in the dataset with the data in the original database. Here is a code example I wrote that iterates through all of the data in a disconnected dataset:

' Visual Basic .NET code.
' Console application.
' References:
'   System
'   System.Data
'   System.XML

Imports System.Data.OleDb                   ' For OleDb objects.
Imports Microsoft.VisualBasic.ControlChars  ' For CrLf constant.

Module Module1

    Sub Main()

        ' Create and initialize OleDbDataAdapter and DataSet objects.
        Dim objAdapter As New OleDbDataAdapter _
            ("SELECT * FROM Products", _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "User ID=Admin;" & _
            "Data Source=C:\Program Files\Microsoft" & _
            "Office\Office10\Samples\Northwind.mdb")
        Dim objDataSet As New DataSet()

        ' Copy the selected data into a dataset.
        objAdapter.Fill(objDataSet)

        ' List out the data values in the dataset.
        Call ListData(objDataSet)

    End Sub

    Public Sub ListData(ByVal objDataSet As DataSet)

        ' Purpose: Lists out the data values for a given dataset. 
        ' Accepts: objDataSet - The dataset. 

        Dim objTable As DataTable
        Dim intRow, intColumn As Integer

        ' There may be more than one table in the dataset. 
        For Each objTable In objDataSet.Tables

            With objTable

                Console.Write("Data in table named " & _
                    .TableName & ":" & CrLf)

                ' List out column names first. 
                For intColumn = 0 To .Columns.Count - 1

                    If intColumn = .Columns.Count - 1 Then
                        Console.Write(.Columns(intColumn).ColumnName _
                            & CrLf)
                    Else
                        Console.Write(.Columns(intColumn).ColumnName _
                            & ", ")
                    End If

                Next intColumn

                ' List out the data by rows...
                For intRow = 0 To .Rows.Count - 1

                    ' ...then by fields in each row.
                    For intColumn = 0 To _
                        .Rows(intRow).ItemArray.Length - 1

                        If intColumn = _
                                .Rows(intRow).ItemArray.Length - 1 Then
                            Console.Write(.Rows(intRow). _
                                ItemArray(intColumn) _
                                & CrLf)
                        Else
                            Console.Write(.Rows(intRow). _
                                ItemArray(intColumn) & ", ")
                        End If

                    Next intColumn

                Next intRow

            End With

        Next objTable

        ' Pause so user can see the data in the console window. 
        Console.Write("Press any key to continue...")
        Console.Read()

    End Sub

End Module

Here is how the code works:

Again, the Imports code (such as Imports System.Data.OleDb) helps cut down the number of keystrokes required to access an object or its members. Similarly, OleDbDataAdapter and DataSet objects are declared and initialized to represent the adapter between the database and the dataset, as well as the dataset, respectively. The OleDbAdapter object's FillData method copies the data in the database to the dataset. The workhorse behind this code is in the ListData subroutine.
Using the DataSet object's Tables property, the outermost For...Each loop iterates through each table in the dataset, returning each table as a DataTable object.
The DataTable object's Columns property returns a DataColumnCollection representing all of the columns in a table. When the Columns property is used with an index number (as shown in conjunction with the DataColumnCollection collection's Count property), a DataColumn object is returned. The DataColumn object's ColumnName property returns the name of the column.
Similarly, the DataTable object's Rows property returns a DataRowCollection representing all of the rows in a table. When the Rows property is used with an index number (as shown in conjunction with the DataRowCollection collection's Count property), a DataRow object is returned. The DataRow object's ItemArray property returns an array of type Object representing each value in the data row. When used with an index number, the ItemArray property returns an individual data field value (as shown in conjunction with the DataColumnCollection collection's Count property).

For More Information

For more information on ADO.NET, see the following:

ADO.NET (list of links)
ADO.NET for the ADO Programmer (technical article)
Accessing Data with ADO.NET (list of links)
Views and Filters (technical article)
Data Relations and Relatives (technical article)

And, as always, check in regularly at the Office Developer Center for information and technical articles on Office solution development.

Paul Cornell works for the MSDN Online Office Developer Center and the Office developer documentation team and also writes the Office Power User Corner column for the Office Assistance Center site. He spends his free time with his wife and daughter.

Unlocking Microsoft Access Data with ADO.NET

Determining When ADO.NET Is the Best Choice

Getting Started with ADO.NET

Can I Use ADO.NET in Office VBA?

Using the Visual Studio .NET Data Access Tools

Data Connections

Database Projects

Data Form Wizard

Extended ADO.NET Code Examples

Accessing Data in Read-Only, Forward-Only Format Using the DataReader Object

Working with Datasets Using the DataAdapter, DataSet, DataTable, DataRow, and DataColumn Objects

For More Information

Additional resources