|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer|
How to: Connect to an Access Database Using the SqlDataSource Control
You can connect to a Microsoft Access database using thecontrol. 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 property, such as the control.
You can also connect to an Access database using thecontrol, which supports a 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 theelement, add a 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.
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:
If 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|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 providerwhen 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:
Set to a SQL select statement for retrieving data, as in the following example:
SelectCommand="Select * From Customers"
Set to the name of the connection string that you created in the Web.config file.
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.