Retrieving Data Using the AccessDataSource Web Server Control
The AccessDataSource control enables you to retrieve data from a Microsoft Access database (.mdb file). You can then display the data in data-bound controls, such as the GridView, FormView, and DetailsView controls.
The AccessDataSource control inherits the SqlDataSource class and replaces the ConnectionString property with a DataFile property to make it more convenient to connect to a Microsoft Access database. The AccessDataSource control uses the System.Data.OleDb provider to connect to Access databases using the Microsoft.Jet.OLEDB.4.0 OLE DB provider.
Connecting to a Database using the AccessDataSource Control
The AccessDataSource control connects to the Microsoft Access database file (.mdb file) identified in the DataFile property. You can set the DataFile property to a universal naming convention (UNC) path that points to an Access database file. The following example demonstrates how you can use a root-relative path to identify an Access database that is located in the App_Data folder of the current Web application.
<asp:AccessDataSource
id="AccessDataSource1"
DataFile="~/App_Data/Northwind.mdb"
runat="server"
SelectCommand="SELECT EmployeeID, LastName, FirstName FROM Employees">
</asp:AccessDataSource>
If you are storing an Access database file with your Web application, it is recommended that you store it in the App_Data folder to keep the database private. ASP.NET does not allow files in the App_Data folder to be returned if requested directly. The ASP.NET process identity must be granted read and write file permissions to the Access databases stored in the App_Data folder. For information on the ASP.NET process identity, see Configuring ASP.NET Process Identity.
The AccessDataSource control sets the ProviderName property of the base SqlDataSource class to the System.Data.OleDb provider and connects using the Microsoft.Jet.OLEDB.4.0 OLE DB provider. You cannot set the ProviderName or ConnectionString properties of the AccessDataSource control.
Note
The AccessDataSource will not connect to an Access database that is password-protected; to retrieve data from a password-protected Access database, use the SqlDataSource control.
Selecting Data Using the AccessDataSource Control
You can specify an SQL query for the AccessDataSource control to execute by setting its SelectCommand property as shown in the following code example.
<form id="form1" runat="server">
<asp:AccessDataSource
id="EmployeesAccessDataSource"
DataFile="~/App_Data/Northwind.mdb"
runat="server"
SelectCommand="SELECT EmployeeID, FirstName, LastName FROM Employees" />
<asp:GridView
id="EmployeesGridView"
runat="server"
AutoGenerateColumns="True"
DataSourceid="EmployeesAccessDataSource" />
<form id="form1" runat="server">
<asp:AccessDataSource
id="EmployeesAccessDataSource"
DataFile="~/App_Data/Northwind.mdb"
runat="server"
SelectCommand="SELECT EmployeeID, FirstName, LastName FROM Employees" />
<asp:GridView
id="EmployeesGridView"
runat="server"
AutoGenerateColumns="True"
DataSourceid="EmployeesAccessDataSource" />
You can return results from a Microsoft Access query by setting the SelectCommandType property of your SelectCommand to StoredProcedure as shown in the following code example.
<asp:AccessDataSource
id="InvoiceAccessDataSource"
DataFile="~/App_Data/Northwind.mdb"
runat="server"
SelectCommand="[Employee Sales By Country]"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="Beginning Date" Type="DateTime" defaultValue="1/1/1997" />
<asp:Parameter Name="Ending Date" Type="DateTime" defaultValue="1/31/1997" />
</SelectParameters>
</asp:AccessDataSource>
<asp:GridView
id="InvoiceGridView"
runat="server"
AutoGenerateColumns="True"
DataSourceid="InvoiceAccessDataSource" />
<asp:AccessDataSource
id="InvoiceAccessDataSource"
DataFile="~/App_Data/Northwind.mdb"
runat="server"
SelectCommand="[Employee Sales By Country]"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="Beginning Date" Type="DateTime" defaultValue="1/1/1997" />
<asp:Parameter Name="Ending Date" Type="DateTime" defaultValue="1/31/1997" />
</SelectParameters>
</asp:AccessDataSource>
<asp:GridView
id="InvoiceGridView"
runat="server"
AutoGenerateColumns="True"
DataSourceid="InvoiceAccessDataSource" />
For information on modifying data using the AccessDataSource control, see Modifying Data using the SqlDataSource Control; that topic applies to working with the AccessDataSource control because the control inherits the capability of the SqlDataSource control and implicitly makes use of the System.Data.OleDb provider.
Using Parameters with the AccessDataSource Control
You can use parameterized queries for commands and supply parameters at run time. Additionally, you can specify parameters at run time when calling a Microsoft Access query. (To call a Microsoft Access query, you set the command type property for the command to StoredProcedure). For more information, see Using Parameters with Data Source Controls.
Because the AccessDataSource control extends the SqlDataSource class and uses the System.Data.OleDb provider, you specify parameter placeholders using the "?" placeholder character. The System.Data.OleDb provider does not support named parameters; instead, the parameter values are applied in the order they are specified in the parameters collection. You must ensure that the order of the parameters in the parameters collection matches the order of the parameter placeholders in your SQL statement or Microsoft Access query. For more information and examples, see Using Parameters with the SqlDataSource Control.