How to: Connect to an Access Database Using the SqlDataSource Control
You can connect to a Microsoft Access database using the SqlDataSource control. To do so, you need a connection string and an Access data file. You can then use the SqlDataSource control to provide data to any data-bound control that supports the DataSourceID property, such as the GridView control.
.gif "Note") Note |
|---|
You can also connect to an Access database using the AccessDataSource control, which supports a DataFile property for specifying the name of the .mdb file to connect to. However, connecting to an Access database using the SqlDataSource control allows you to specify additional connection properties, such as authentication credentials. In general, if the Access database you are connecting to requires a password, you should connect to it with the SqlDataSource control, store the authentication credentials in a connection string in the Web.config file, and protect the connection string by encrypting it. |
To configure a connection string for Access in the Web.config file
Open the Web.config file in the root directory of your ASP.NET application. If you do not have a Web.config file, create one.
In the Configuration element, add a ConnectionStrings element if one does not already exist.
Create an add element as a child of the ConnectionStrings element, defining the following attributes:
name Set the value to the name that you want to use to reference the connection string.
name="CustomerDataConnectionString"connectionString Assign a connection string with the appropriate provider for Microsoft Access, the location of your Access data file, and authentication information, if applicable. Your connection string might look like the following:
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb;"NoteIf you store the Access .mdb file in the Web site's App_Data directory, which is recommended for enhanced security, you can specify the location using the syntax
|DataDirectory|path. The|DataDirectory|string resolves at run time to the App_Data folder of your Web site.providerName Assign the value "System.Data.OleDb", which specifies that ASP.NET should use the ADO.NET provider System.Data.OleDb when making a connection with this connection string.
The connection string configuration will be similar to the following:
<connectionStrings> <add name="CustomerDataConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|Northwind.mdb" providerName="System.Data.OleDb" /> </connectionStrings>Save the Web.config file and close it.
To reference the Access connection string from a SqlDataSource control
In the page in which you want to connect to the Access database, add a SqlDataSource control.
In the SqlDataSource control, set the following properties:
SelectCommand Set to a SQL select statement for retrieving data, as in the following example:
SelectCommand="Select * From Customers"ConnectionString Set to the name of the connection string that you created in the Web.config file.
ProviderName Set to the name of the provider that you specified in the Web.config file**.**
The following example shows a SqlDataSource control configured to connect to an Access database.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CustomerDataConnectionString %>" ProviderName="<%$ ConnectionStrings:CustomerDataConnectionString.ProviderName %>" SelectCommand="SELECT * FROM Customers" />You can now bind other controls, such as the GridView control, to the SqlDataSource control.