Using an Access 2007 database with ASP.NET 3.5 and Expression Web 3

One question that we occasionally hear from Expression Web users is “How do I use my Access 2007 database on my ASP.NET site?”

First, the not so great news: Expression Web 3 supports ASP.NET 2.0, 3.0, and 3.5. These versions of ASP.NET do not support Access 2007 data connections.

Now, the great news: ASP.NET 4.0 supports these data connections – and Expression Web 4 (not yet released) supports ASP.NET 4.0. Even better – as we announced at the MIX 2010 Conference, if you have purchased Expression Web 3, you will get Expression Web 4 for free! (See here for details).

Until Expression Web 4 is available, you can use the following steps to use your Access 2007 database in a site in Expression Web 3.

1. First, your system and server must have the 2007 Office System Driver: Data Connectivity Components installed. Many ASP.NET hosts have this installed, such as DiscountASP. Once this is installed, you’re ready to start working.
   
2. Drag a SqlDataSource control from the Toolbox panel and drop it into the Design view of your page. (The SqlDataSource control is under the Data category in the section of ASP.NET controls,)
   
    
   
3. In the Design view of your page, next to the SqlDAtaSource control, click Configure Data Source.
   
4. On the Choose your Data Connection screen, click New Connection.
   
    
   
5. In the Choose Data Source screen, set Data Source to <other>, and Data Provider to .NET Framework Data Provider for OLE DB, and click OK.
   
    
   
6. In the Connections Properties dialog, click the OLE DB Provider menu and  select Microsoft Office 12.0 Access Database Engine OLE DB Provider. In the Server or file name box, enter the full path to your database, and then click Test Connection”. If your test connection succeeded, you’re on the right path so far! Click OK in the Connection Properties dialog, and then click Next in the Configure Data Source dialog.
    

Note: If you just installed the 2007 Office Driver: Data Connectivity Components you’ll need to restart Expression Web 3 before you see “Microsoft Office 12.0 Access Database Engine OLE DB Provider” in the list. Also, you may need to modify your “Server or file name:” information once you upload your site to a server.

7. Save your new Connection String and click Next.
   
    
   

Now it’s time to configure the query for your database connection.

8. Select the columns you want to display from your database, and click Next. In my example, I selected Amount, DonorName, and CampaignName.
   
   
   
9. Click Test Query. If the test was successful, then click Finish.
   
   
   

Now you’re ready to add any ASP.NET data control to your page and set up the control to your the data source you just added to the page. In the following screensheet, you can see the GridView control that I placed on my page and set to use my SqlDataSource1 data source.

 

And now I’ll preview my page in SuperPreview to make sure the data is displaying as I want.



Once Expression Web 4 is available, I’ll write a follow-up post with the (much easier!) steps on how to use an Access 2007 database as a data source in your site.



Justin Harrison, Program Manager
Microsoft Expression Web