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.
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.
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,)
In the Design view of your page, next to the SqlDAtaSource control, click Configure Data Source.
On the Choose your Data Connection screen, click New Connection.
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.
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.
Save your new Connection String and click Next.
Now it’s time to configure the query for your database connection.
Select the columns you want to display from your database, and click Next. In my example, I selected Amount, DonorName, and CampaignName.
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
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 TinaAnonymous
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 KumatAnonymous
September 19, 2010
Can I use Expression Web 2 to connect to an Office 2007 or Office 2010 access data base? If so, how. ThanksAnonymous
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 removedAnonymous
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 AlanAnonymous
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.aspxAnonymous
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 webAnonymous
March 20, 2014
A simple asp.net database tutorial asp.net-informations.com/.../asp-data-providers.htm sureashAnonymous
August 14, 2014
axegahukar.blogspot.in/.../how-to-connect-database-in-aspnet.html