Connecting to MS Access using the OLE DB .NET Data Provider

To make a connection to a database that was created with MS Access, you don't need to have Access installed on your machine, and the code we'll use is really very similar to the code you've already seen. The difference is that we'll be using the OLE DB .NET data provider (which includes the OleDbConnection object), and assembling a connection string that specifies the MDB file created by Access, and the OLE DB-compliant JET provider.

JET is the name of the data engine used by Access. In the above configuration, information flows from the Access database, through the JET engine, and then through ADO.NET's OLE DB JET provider.

The diagram below displays the relationship of ADO.NET objects as they apply to using an Access file as a data source. Notice that we use specific OLE DB objects, except for the DataSet and DataTable objects, which are generic.

Access Connection Syntax

Despite the choice of a different data source, creating a connection to an Access database involves the same three steps as before: import the appropriate namespaces; create the connection string; instantiate the connection object. When we connect to Access, we use the OLE DB.NET data provider, so we must import the System.Data.OleDb namespace alongside the System.Data namespace.

   <%@ Import namespace="System.Data" %>
   <%@ Import namespace="System.Data.OleDb" %>

The connection string is the part of the process that exhibits most changes from what we had with SQL Server. When you're dealing with the OLE DB .NET data provider, it must contain at least two pieces of information: the type of provider we're going to use, and the name of the Access file:

   Dim strConnection As String = "Provider=MyProvider; " & _
                                 "data source=MyDataSource;"

Apart from the fact that it belongs to a different class, however, creating the connection object involves exactly the same operation we used before:

   Dim objConnection As New OleDbConnection(strConnection)

A key thing to be aware of here is that when you connect to an Access database, you must specify the file in which the data is stored. With enterprise-capable database management systems (Oracle, Microsoft SQL Server, IBM DB2, etc.), you don't need to worry about how the information in the database is stored. ADO.NET connections to these latter kinds of data stores can switch between different databases within the one connection. If we want to connect to a different Access database, we must create a new connection object.