Sunday, May 18, 2008

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"
="<%$ ConnectionStrings:myConn %>"
="<%$ ConnectionStrings:myConn.ProviderName %>"
="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"
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-

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.


No comments: