[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1069
  • 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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