JavaScript Editor jscript editor     Web designer 



Main Page

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.

NoteNote

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

  1. 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.

  2. In the Configuration element, add a ConnectionStrings element if one does not already exist.

  3. 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.

      В CopyCode imageCopy Code
      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:

      В CopyCode imageCopy Code
      connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb;"
      NoteNote

      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|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:

    В CopyCode imageCopy Code
    <connectionStrings>
      <add name="CustomerDataConnectionString" 
        connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
          Data Source=|DataDirectory|Northwind.mdb"
        providerName="System.Data.OleDb" />
    </connectionStrings>
  4. Save the Web.config file and close it.

To reference the Access connection string from a SqlDataSource control

  1. In the page in which you want to connect to the Access database, add a SqlDataSource control.

  2. In the SqlDataSource control, set the following properties:

    • SelectCommandВ В В Set to a SQL select statement for retrieving data, as in the following example:

      В CopyCode imageCopy Code
      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.

    В CopyCode imageCopy Code
    <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.

See Also

Reference

AccessDataSource

Other Resources

Understanding ASP.NET Data Architecture - deleted



JavaScript Editor jscript editor     Web designer