Solved

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

Posted on 2010-08-24
44
716 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
Comment Utility
<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
Comment Utility

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
Comment Utility
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
 

Author Comment

by:chokka
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility


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

Expert Comment

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

Author Comment

by:chokka
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

@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
Comment Utility
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
Comment Utility
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
Comment Utility


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
Comment Utility

|DataDirectory| is not mapping to ...

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

Expert Comment

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

Author Comment

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

Expert Comment

by:13598
Comment Utility
Does the directory exist?
C:\Enrollment Form\EnrollmentApp\EnrollmentApp\App_Data
0
 

Author Comment

by:chokka
Comment Utility


Yes, Please check the screen shot
Directory.JPG
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:Zhaolai
Comment Utility
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
Comment Utility


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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Have you tested before suggesting the last two piece of comment ?

0
 
LVL 17

Expert Comment

by:Zhaolai
Comment Utility
This link may be helpful:

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

Expert Comment

by:13598
Comment Utility
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
Comment Utility



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
Comment Utility
What value do you get with this?
path=System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"App_data")
 
0
 
LVL 17

Expert Comment

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

Expert Comment

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

0
 
LVL 16

Expert Comment

by:kris_per
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0
 
LVL 17

Expert Comment

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

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Retain selection in datagridview 2 22
Free Alternative to JIRA 4 52
Vb.Net Menu 1 21
Round a string to two digits 12 23
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now