How to connect with MS Access Database?

[cablecar]

Hi folks,

Is anyone using MS Access database for their apps? I'm wondering how do I connect to an access database? I get the ff error:
Unable to connect to database. Not a valid file name. Please also: Search online help for Configure an application with Microsoft Access database for IIS 7.


This is the connection string I used:
<add name="MyDatabase.mdb1" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=http:\\www.domain.net\_database\mydatabase. mdb;Jet OLEDB:Database Password=mypassword;Jet OLEDB:Database Locking Mode=1;Mode=16" providerName="System.Data.OleDb" />

At my local machine, I just gave full permission to Network Service and provided the Data Source's physical path (e.g. 'E:\mydatabase.mdb' unlike my connection string above.) and everything works fine.

It's a small application for a non profit association, so a free database such as MS Access is very appropriate.

I was advised that I would need to have a SQL Database to use the MDF tool or connect to it programmatically...but how to?

Thanks in advance for the help!:D

[wisemx]

Hi,
These are the "name spaces" and a valid working example I used on a DASP server:

<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>

Dim strConnection As String = "provider=Microsoft.Jet.OLEDB.4.0;data source=" + Server.MapPath("../_database/mydatabase.mdb")

[cablecar]

Hi,

Thanks for the response. I'm afraid using Server.MapPath is not the best way for me since the base of my application was produced by a code generator. I would have to edit every connection string.

I tried changing the Data Source value to:
Data Source=E:\web\myaccountname\_database\mydatabase.m db
but it gives me an internal server error.
E:\web\myaccountname\ is my root path

[CrystalCMS]

It's possible MapPath can still be used. Here's one method - something like this in your web.config:
<connectionStrings>
<add name="MyAccessDB" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<%=myAccessDBPath%>;Persist Security Info=False;"/>
</connectionStrings>

myAccessDB would be a prop or method that performs a call to MapPath to get the correct path to the file. The best place for this to go would probably be in a global.asax but depending on the rest of your code it might need to go at page level. You could also store the resulting connection string in application cache for later use.

[CrystalCMS]

I'm sorry disregard my last post - it was obviously far too early in the morning over here. You could try something like this:
<connectionStrings>
<add name="MyAccessDB" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Persist Security Info=False;"/>
</connectionStrings>

using System;
using System.Configuration;

namespace TestWebApplication
{
public class Global : System.Web.HttpApplication
{

protected void Application_Start(object sender, EventArgs e)
{
string MyAccessDBConnStr = ConfigurationManager.ConnectionStrings["MyAccessDB"].ConnectionString;
MyAccessDBConnStr = string.Format(MyAccessDBConnStr, Server.MapPath("../_database/MyAccessDB.mdb"));
Application["MyAccessDBConnStr"] = MyAccessDBConnStr;
}