Solved

How to set the Connection String for MS Access Database which is in App_Data Folder

Posted on 2010-08-24
44
756 Views
Last Modified: 2013-11-27

Instead of giving Physical file path, i need to set the connection string on App_Data as Default Folder ..! This can help me while deploying the application


<add name="MembershipDB"
           connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;"
           providerName="System.Data.OleDb" />
0
Comment
Question by:chokka
  • 17
  • 15
  • 8
  • +1
44 Comments
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33513454
<add name="MembershipDB"
           connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=~/App_Data/myAccess2007file.accdb;Persist Security Info=False;"
           providerName="System.Data.OleDb" />

0
 
LVL 16

Expert Comment

by:kris_per
ID: 33513502

You can use the keyword '|DataDirectory|' in the Data Source of the connection string...

<add name="MembershipDB"
          connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|/myAccess2007file.accdb;Persist Security Info=False;"
          providerName="System.Data.OleDb" />
0
 
LVL 16

Expert Comment

by:13598
ID: 33514686
You can try this:
<add name="MembershipDB"
           connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|myAccess2007file.accdb;Persist Security Info=False;"
           providerName="System.Data.OleDb" />  
providerName="System.Data.OleDb"></add>
 
</connectionStrings>
 
By Default |DataDirectory| maps the path of database file placed inside the default App_Data directory. After |DataDirectory| add up the name of MS Access Database filename as shown in above example (no slash).  
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:chokka
ID: 33514799

As per Zhaolai:
I am facing this error ..!

{"'C:\Enrollment Form\EnrollmentApp\EnrollmentApp\bin\Debug\~\App_Data\EnrollmentDB.accdb' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."}
0
 

Author Comment

by:chokka
ID: 33514823
As per 13598 :

I am facing
 the following message

"Could not find installable ISAM."
0
 
LVL 16

Accepted Solution

by:
13598 earned 250 total points
ID: 33514846
Yeah I just noticed  my copy/pasting abilities ...
Try this=>

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

0
 

Author Comment

by:chokka
ID: 33514914
I have not copied and pasted .. your code ..

I just typed it ..!

This is the actual connection string ..

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

And i am using MS Office 2007. Was it the right version ?
0
 
LVL 16

Expert Comment

by:13598
ID: 33514938
You have two equal signs after Data Source  remove one please =>
 
Data Source==|DataDirectory|EnrollmentDB.accdb;Persist Security Info=False;" providerName="System.Data.OleDb" />
 
0
 
LVL 16

Assisted Solution

by:kris_per
kris_per earned 250 total points
ID: 33514966


I hope |DataDirectory| method mentioned in the 2nd comment (id: 33513502) will work....
0
 
LVL 16

Expert Comment

by:13598
ID: 33514997
It won't work with a slash as mentioned in the 2nd comment....
0
 

Author Comment

by:chokka
ID: 33515029
Thanks ..!

Thanks for correcting the mistake of using " == "

|DataDirectory| is pointing to Debug Folder .. and not to App_Data Folder.

i am working on VS 2005 / VB.Net project
0
 

Author Comment

by:chokka
ID: 33515034
Now it throws the exception as follows ...

Could not find file 'C:\Enrollment Form\EnrollmentApp\EnrollmentApp\bin\Debug\EnrollmentDB.accdb'.
0
 
LVL 16

Expert Comment

by:13598
ID: 33515042
You must be in debug mode. Try changing it to release and it will point to the release folder.
0
 
LVL 16

Expert Comment

by:kris_per
ID: 33515060

@13598, Thanks for the correction in my solution...

It was a copy-paste typo in my comment.....the right code (without slash) is:

<add name="MembershipDB"
          connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|myAccess2007file.accdb;Persist Security Info=False;"
          providerName="System.Data.OleDb" />
0
 
LVL 16

Expert Comment

by:13598
ID: 33515070
Did it work once and now it doesn't? Or it never worked. I am not clear about that point.
0
 
LVL 16

Expert Comment

by:13598
ID: 33515082
Why does EnrollmentApp show up twice?
'C:\Enrollment Form\EnrollmentApp\EnrollmentApp\bin\Debug\EnrollmentDB.accdb'
What should the path be?
 
0
 

Author Comment

by:chokka
ID: 33515111


This is the file path

C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data

and file name is

C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data\EnrollmentDB.mdb
0
 

Author Comment

by:chokka
ID: 33515125

|DataDirectory| is not mapping to ...

C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data\EnrollmentDB.mdb
0
 
LVL 16

Expert Comment

by:13598
ID: 33515159
Add this to your code and let me know what you get:
messagebox.show(Environment.GetFolderPath(SpecialFolder.ApplicationData) )
0
 

Author Comment

by:chokka
ID: 33515207
Could not find file 'C:\Enrollment Form\EnrollmentApp\EnrollmentApp\bin\Debug\EnrollmentDB.accdb'.
0
 
LVL 16

Expert Comment

by:13598
ID: 33515229
Does the directory exist?
C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data
0
 

Author Comment

by:chokka
ID: 33515298


Yes, Please check the screen shot
Directory.JPG
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33515321
Try this:

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

Found in the following link:

http://www.connectionstrings.com/access
 
0
 

Author Comment

by:chokka
ID: 33515358


Both Zhaolai and 13598 are same ..!

Error Message :-  Could not find file 'C:\Enrollment Form\EnrollmentApp\EnrollmentApp\bin\Debug\EnrollmentDB.accdb'.


Physical file is located in this  file Path C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data - Which is .mdb
0
 
LVL 16

Expert Comment

by:13598
ID: 33515398
Try this:
 <connectionStrings>
    <add name="MembershipDB" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|EnrollmentDB.mdb;Persist Security Info=False;" providerName="System.Data.OleDb" />
  </connectionStrings>

 
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33515417
Notice that there is a backward slash "\" before DB name in my posted code. Also the database name in my code is EnrollmentDB.mdb, not EnrollmentDB.accdb.
 
0
 

Author Comment

by:chokka
ID: 33515450
13598 .. I tried a lot ..

in all the ways .. it doesnt work ..!

At present, i am running the app .. by providing the actual file path ..!


0
 
LVL 16

Expert Comment

by:13598
ID: 33515474
It is your connection string.
If you use .mdb then your provider needs to be Microsoft.Jet.OLEDB.4.0
as in:
<connectionStrings>
   <add name="MembershipDB" connectionString="Provider= Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|EnrollmentDB.mdb;Persist Security Info=False;" providerName="System.Data.OleDb" />
 </connectionStrings>
 
 
Microsoft.ACE.OLEDB.12. is for accdb
0
 
LVL 16

Expert Comment

by:13598
ID: 33515501
Or if you have a new Jet driver that will be fine. But you need to use the Jeg.OLEDB not the ACE.OLEDB.
0
 
LVL 16

Expert Comment

by:13598
ID: 33515507
Or if you have a newer Jet driver that will be fine. But you need to use the Jet.OLEDB not the ACE.OLEDB.
0
 

Author Comment

by:chokka
ID: 33515566
Have you tested before suggesting the last two piece of comment ?

0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33515614
This link may be helpful:

http://www.connectionstrings.com/access-2007
 
0
 
LVL 16

Expert Comment

by:13598
ID: 33515637
Not with 2007. I don't remember seeing what version of Access you are working with (maybe it is somewhere here and I missed it).At first I thought you were using Access 2007 because of the file extension but when you switched to mdb it could be any version. I am not sure if you are using vb.net or asp.net either.
And I don't know what your specialfolder.applicationdata returns either.
0
 

Author Comment

by:chokka
ID: 33515721



With this connection string :

  <connectionStrings>
    <add name="MembershipDB" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data\EnrollmentDB.mdb;Persist Security Info=False;" providerName="System.Data.OleDb" />
  </connectionStrings>

Application works perfectly fine.

EnrollmentApp\EnrollmentApp\App_Data\EnrollmentDB.mdb - > Is the Root Directory of the Working Folder.

If i deployed the application to my client machine or end users, we need the database  to be mapped automatically from DataDirectory.

0
 
LVL 16

Expert Comment

by:13598
ID: 33515737
What value do you get with this?
path=System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"App_data")
 
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33515751
Have you checked with the link in my last post?
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33515882
Check out this link. It may be helpful:
http://www.connectionstrings.com/access-2007

0
 
LVL 16

Expert Comment

by:kris_per
ID: 33515928
@chokka

The DataDirectory keyword is mainly used in web.config (for web apps where asp.net has a specifically named folder 'App_Data'). But during the discussion I understand yours is windows forms app and I think windows forms app has no concept of special App_Data folder.

So for win forms app, In the dev machine, the app.config will have the connection string like:

<add name="MembershipDB"
          connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\App_data\mydb.mdb;Persist Security Info=False;"
          providerName="System.Data.OleDb" />

(note ..\..\ in the connection string to denote the App_data folder in the project folder)

In the installer for this app, it will be a different app.config that will have the connection string like:

<add name="MembershipDB"
          connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=App_data\mydb.mdb;Persist Security Info=False;"
          providerName="System.Data.OleDb" />

i.e. database is installed in a subfolder named App_folder. so the folder structure after installation is:
- MyApp folder
   -- App_data subfolder
         -- mydb.mdb
   -- myapp.exe
   -- myapp.exe.config
   -- other files....

This is how I used it once...hope this helps....


0
 
LVL 16

Expert Comment

by:13598
ID: 33516080
Datadirectory works with Windows Apps too it is used with .net framework apps. The key is how are you deploying your app? Are you using ClickOnce or using a setup project? And also datadirectory during testing and when running the app from within the IDE is not going to be the same as when the app is deployed and running outside the IDE.
I am surprised that Zhaolai first comment didn't work since it would be more of a direct approach in your case.
 
0
 

Author Comment

by:chokka
ID: 33516135
1) This is windows App

2) I am developing a MSI Click Once Deployment Tool

Issue is not yet resolved ..

On using this ..\..\App_data\, still i am not able to detect the database
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33516427
This is how I did it:

 1. in VB.NET IDE, right click on the project->Properties->Compile, change Build output path to bin\. Save the change.
 
 2. In Windows Explorer, browse to your project's bin folder, create a sub folder App_Data. Then copy your database over.
 
3. ConnectionString like this:
        <add name="MembershipDB"
            connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\App_Data\EnrollmentDB.mdb"
            providerName="System.Data.OleDb" />
 
4. Run app in debug mode.
 
5. When you deploy, make sure you create the App_Data sub folder is under the .exe file.

0
 
LVL 16

Expert Comment

by:13598
ID: 33516853
I guess my questions would be:
 why do you have your db in the App_data folder ? App_data folder you create with asp.net apps and you can add dbs to it and set certain things from within your solution/project. This is a windows app what is the reason for not putting the db in the applicationdata specialfolder for example or one of the special folders?
how did you create the folder?
How did you put your db in the folder (just copied it using explorer?)
0
 

Author Closing Comment

by:chokka
ID: 33526692
Thanks
0
 
LVL 17

Expert Comment

by:Zhaolai
ID: 33527567
Is the issue resolved? If so, would you please post the solution to share with the rest of us?
Thanks.

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question