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 this, you need a connection string and an Access data file. Then, you can use the SqlDataSource control to provide data to any data-bound control that supports the DataSourceID property, such as the GridView control.
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 lets you specify additional connection properties, such as authentication credentials. Generally, if the Access database that 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 help protect the connection string by encrypting it.
To connect to an Access database using the SqlDataSource control
In Microsoft Visual Studio, open a Web site, add an ASP.NET page (Web Form), and switch to Design view.
From the Data group in the Toolbox, drag a SqlDataSource control onto the page.
On the SqlDataSource Tasks shortcut menu, click Configure Data Source.
- If the SqlDataSource Tasks shortcut menu does not appear, right-click the SqlDataSource control, and then click Configure Data Source.
Click New Connection.
- If the Choose Data Source dialog box appears, click Microsoft Access Database File, and then click Continue.
In the Add Connection dialog box, click Change, in the Change Data Source dialog box, click Microsoft Access Database File, and then click OK.
In the Database file name box, enter a path to the Access database, and then under Log on to the database, enter your logon credentials, if they are required.
Optionally, click Test connection to verify that the connection to the Access database succeeds.
Click OK.
Notice that in the Configure Data Source - <Datasourcename> dialog box, your new connection is selected.
Click Next.
Select the Yes, save this connection as check box, enter a name for your connection for when the connection is stored in the application configuration file, and then click Next.
Select the database table, view (Access Query), or stored procedure (Access Query) from which to retrieve results or enter your own SQL statement.
Optionally, you can click WHERE to supply search criteria and ORDER BY to specify sort order.
- If you want to support insert, update, and delete operations, click Advanced, and then select Generate INSERT, UPDATE, and DELETE statements for your SqlDataSource control. You can also select Use optimistic concurrency to specify that you want the commands to determine whether the data has been modified before an update or delete operation is performed.
To test your query, click Next, and then click Test Query.
Click Finish.
You can now drag a data-bound control, such as a GridView control, onto the page and specify your SqlDataSource control as the data source.