Partager via

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

  • Article

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

Comments

  • Anonymous
    March 29, 2010
    Hi Justin, great that you have mentioned that users can buy Expression Web 3.0 now and get the upgrade to Expression Web 4.0 for free (and current owners) when it comes out. However this is not mentioned on the ms ew site http://www.microsoft.com/Expression/products/Upgrade.aspx there or the overview or upgrade info. Can that be remedied since it is now public knowledge having been published here? thanks Tina

  • Anonymous
    March 29, 2010
    If there is one thing that should be avoided at all cost it's using Access as a database backend for your website.

  • Anonymous
    June 02, 2010
    Hi, I am new to database/Web programming. I have a small question. Is it possible to access MS Access database using just ip address, (and NO physical path), id and password ? If Yes, can you please point me there. Thanks, Piyush Kumat

  • Anonymous
    September 19, 2010
    Can I use Expression Web 2 to connect to an Office 2007 or Office 2010 access data base? If so, how. Thanks

  • Anonymous
    September 29, 2010
    Where's the step in your explanation where you name the data source as SqlDataSource1?

  • Anonymous
    October 02, 2010
    The comment has been removed

  • Anonymous
    December 17, 2010
    Expression 4 is out but I can't make it work with ACCESS 2010 databases. Any ideas how?

  • Anonymous
    March 25, 2011
    Thanks for the lesson I followed it with success. I tried taking it to the next level as I need to apply multiple searches. For example if I had a database full of different couloured shapes and different sizes using this lesson I can search one attribute say color for example. I would like to be able to filter further and say show me only red squares or add another filter to say show me small red squares. How do I go about doing this? I want a single search page with the three different attributes so I can pick and choose. Thanks in advance for your help Alan

  • Anonymous
    April 05, 2011
    You can add WHERE clauses to your SELECT statement when you are configuring you data source. On the Configure the Select Statement step, click on the WHERE... button. You will then see the Add WHERE Clause dialog. On that dialog, you can add any number of colums to filter with and configure the source of the value used to filter. For example, you can add the Color field, using the "=" operator and set the Source to be QueryString. Set the QueryString field to "Color" and then request the page using http://site/mypage.aspx?Color=Red. You can add any number of fields and use other inputs, like cookie or control values. Hope this helps!

  • Anonymous
    May 05, 2011
    Hey Justin whats up? Lol I was doing a search on getting ASP.NET to work with my Access DB and came across this post only to find it was written by you! I hope all is well at Microsoft best.

  • Anonymous
    January 24, 2012
    Great post. Here’s how you can convert Microsoft Access to web in minutes www.caspio.com/.../convert-ms-access-to-web.aspx

  • Anonymous
    February 09, 2012
    I have two fields in my access database that are url's.  When I import the database into Expression web they show up as text.  Is there anyway I can change the columns to HTML in expression web

  • Anonymous
    March 20, 2014
    A simple asp.net database tutorial asp.net-informations.com/.../asp-data-providers.htm sureash

  • Anonymous
    August 14, 2014
    axegahukar.blogspot.in/.../how-to-connect-database-in-aspnet.html