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

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

  • Comments 13

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

  8. Save your new Connection String and click Next.

    Configure Data Source dialog in Expression Web 3 
  9. Now it’s time to configure the query for your database connection.

  10. 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
  11. Click Test Query. If the test was successful, then click Finish.

    Test Query screen in Expression Web 3
  12. 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

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • 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

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

  • 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

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

    Thanks

  • Where's the step in your explanation where you name the data source as SqlDataSource1?

  • Justin

    When I upload my page and I have to change my file name/server name to make the page work. Can you explain a little more how that works? Do you need the server name and path or just the server name? I can make the set up work using my computer file path, but when I upload the page, thats when things get a little crazy.

    Thanks.

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

  • 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

  • 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!

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

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

  • 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

  • A simple asp.net database tutorial

    asp.net-informations.com/.../asp-data-providers.htm

    sureash

Page 1 of 1 (13 items)