ASP.Net Connecting to An Access 2007 DB

I was fooling around at home and decided to use an Access 2007 DB in a small project. After checking out a few blogs, none of which had the solution completely correct, I got this working. Hopefully this will be useful to someone.

On your ASP.Net aspx page, here is a sample data source. Note, you want to use the SqlDataSource, not the access one. Weird huh?

<asp:SqlDataSource ID="MyDS" runat="server"
ConnectionString="<%$ ConnectionStrings:myConn %>"
ProviderName="<%$ ConnectionStrings:myConn.ProviderName %>"
SelectCommand="select * from mytable"></asp:SqlDataSource>

In your web.config file, add the following connection string. If you haven't added on replace your default ConnectionStrings line with the following-

<add name="myConn"
connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=|DataDirectory\myDB.accdb;
Persist Security Info=False;"
providerName="System.Data.OleDb" />
   

That should get you started.

Also, if you don't have Access 2007 installed on your server, you will need the new Access ODB driver-

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Is all this worth the extra effort then just downsaving your .accdb to an .mdf? From what I see probably not, but here is the info. Not sure which way I will go with things yet for my little project.

Sources:
http://www.connectionstrings.com/
http://weblogs.asp.net/steveschofield/archive/2008/05/03/iis-7-0-access-2007-and-asp-net-2-0.aspx