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,)

    Design view of a SQLDataSource control in a page in Expression Web 3 

  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.

    Choose Your Data Connection screen in Expression Web 3 

  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.

    Choose Data Source screen in Expression Web 3 

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

    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.

    Configure Data Source dialog in Expression Web 3 

    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.

    Configure the Select Statement screen in Expression Web 3

  9. Click Test Query. If the test was successful, then click Finish.

    Test Query screen in Expression Web 3

    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.

    GridView control in the Design view of a page in Expression Web 3 

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

    SuperPreview rendering of the page.

    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.

    dd560693_JustinHarrison(en-us)

    Justin Harrison, Program Manager
    Microsoft Expression Web