ADO.NET 3.5 Cookbook, 2nd Edition

1.12. Connecting to a Microsoft Access Database from ASP.NET

Problem

You know your connection string is correct, but still can't connect to your Microsoft Access database from your ASP.NET application. What are the differences between connecting from a Windows Forms .NET application and an ASP.NET application?

Solution

You must grant the necessary file permissions for accessing a Microsoft Access database engine to the default user account used by ASP.NET.

1. Begin by creating a new ASP.NET Web Application project named ConnectMSAccessDataAspNet.

2. Add the following elements to the Web.config file within the <system.web> element:

   	<authentication mode="Windows" />
   	<identity impersonate="true" />

3. Add a connection string to Northwind 2007.accdbs on the local machine to the configuration file Web.config by adding the following <connectionStrings> element within the <configuration> element:

   	<connectionStrings>
   	    <add name="Northwind2007" providerName="System.Data.OleDb"
   	       connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
   	           Data Source=C:\Documents and Settings\bill\My Documents\
   	           Northwind 2007.accdb;"/>
   	</connectionStrings>

The C# code Default.aspx.cs in the project ConnectMSAccessDataAspNet is shown in Example 1-13.

Example 1-13. File: Default.aspx.cs for ConnectMSAccessDataAspNet solution

using System; using System.Data; using System.Data.OleDb; using System.Configuration; namespace ConnectMSAccessDataAspNet { public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, ...