• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1396
  • Last Modified:

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


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
chokka
Asked:
chokka
  • 17
  • 15
  • 8
  • +1
2 Solutions
 
ZhaolaiCommented:
<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
 
kris_perCommented:

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
 
13598Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
chokkaStudentAuthor Commented:

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
 
chokkaStudentAuthor Commented:
As per 13598 :

I am facing
 the following message

"Could not find installable ISAM."
0
 
13598Commented:
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
 
chokkaStudentAuthor Commented:
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
 
13598Commented:
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
 
kris_perCommented:


I hope |DataDirectory| method mentioned in the 2nd comment (id: 33513502) will work....
0
 
13598Commented:
It won't work with a slash as mentioned in the 2nd comment....
0
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
Now it throws the exception as follows ...

Could not find file 'C:\Enrollment Form\EnrollmentApp\EnrollmentApp\bin\Debug\EnrollmentDB.accdb'.
0
 
13598Commented:
You must be in debug mode. Try changing it to release and it will point to the release folder.
0
 
kris_perCommented:

@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
 
13598Commented:
Did it work once and now it doesn't? Or it never worked. I am not clear about that point.
0
 
13598Commented:
Why does EnrollmentApp show up twice?
'C:\Enrollment Form\EnrollmentApp\EnrollmentApp\bin\Debug\EnrollmentDB.accdb'
What should the path be?
 
0
 
chokkaStudentAuthor Commented:


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
 
chokkaStudentAuthor Commented:

|DataDirectory| is not mapping to ...

C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data\EnrollmentDB.mdb
0
 
13598Commented:
Add this to your code and let me know what you get:
messagebox.show(Environment.GetFolderPath(SpecialFolder.ApplicationData) )
0
 
chokkaStudentAuthor Commented:
Could not find file 'C:\Enrollment Form\EnrollmentApp\EnrollmentApp\bin\Debug\EnrollmentDB.accdb'.
0
 
13598Commented:
Does the directory exist?
C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data
0
 
chokkaStudentAuthor Commented:


Yes, Please check the screen shot
Directory.JPG
0
 
ZhaolaiCommented:
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
 
chokkaStudentAuthor Commented:


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
 
13598Commented:
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
 
ZhaolaiCommented:
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
 
chokkaStudentAuthor Commented:
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
 
13598Commented:
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
 
13598Commented:
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
 
13598Commented:
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
 
chokkaStudentAuthor Commented:
Have you tested before suggesting the last two piece of comment ?

0
 
ZhaolaiCommented:
This link may be helpful:

http://www.connectionstrings.com/access-2007
 
0
 
13598Commented:
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
 
chokkaStudentAuthor Commented:



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
 
13598Commented:
What value do you get with this?
path=System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"App_data")
 
0
 
ZhaolaiCommented:
Have you checked with the link in my last post?
0
 
ZhaolaiCommented:
Check out this link. It may be helpful:
http://www.connectionstrings.com/access-2007

0
 
kris_perCommented:
@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
 
13598Commented:
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
 
chokkaStudentAuthor Commented:
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
 
ZhaolaiCommented:
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
 
13598Commented:
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
 
chokkaStudentAuthor Commented:
Thanks
0
 
ZhaolaiCommented:
Is the issue resolved? If so, would you please post the solution to share with the rest of us?
Thanks.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 17
  • 15
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now