Programming Visual Basic .NET, Second Edition

Using ADO Managed Providers

Example 14-1 used one of the managed providers currently available with ADO.NET: the SQL Managed Provider, the OLE DB Managed Provider, etc. The SQL Managed Provider is optimized for SQL Server and is restricted to working with SQL Server databases. The more general solution is the OLE DB Managed Provider, which will connect to any OLE DB provider, including Access.

You can rewrite Example 14-1 to work with the Northwind database using Access rather than SQL Server with just a few small changes. First, you need to change the connection string:

Dim connectionString As String = _    
"provider=Microsoft.JET.OLEDB.4.0; " & _
"data source = c:\\nwind.mdb"

This query connects to the Northwind database on the C drive. (Your exact path might be different.)

Next, change the DataAdapter object to an ADODataAdapter rather than a SqlDataAdapter:

Dim myDataAdapter As New OleDbDataAdapter( _
  commandString, connectionString)

Also be sure to add an Imports statement for the OleDb namespace:

Imports System.Data.OleDb

This design pattern continues throughout the two Managed Providers; for every object whose class name begins with “Sql,” there is a corresponding class beginning with “ADO.” Example 14-2 illustrates the complete OLE DB version of Example 14-1.

Example 14-2. Using the OLE DB Managed Provider

Option Strict On Imports System Imports System.Drawing Imports System.Collections Imports System.ComponentModel Imports System.Windows.Forms Imports System.Data Imports System.Data.OleDb ...