CODEWITHSUNDEEP
vb.netvisual-studio-2010
aarviii
aarviii

Reputation: 13

How do I retain database connectivity, with my project, when the source file path changes?

I am using a Microsoft Access Database in my project; saved to the bin folder. What can I do, to ensure connectivity to that database, when the file path changes?

Imports System.Data.OleDb Public Class Form3

Dim con As New OleDb.OleDbConnection

Dim da As OleDbDataAdapter

Dim ds As New DataSet

Dim str1 As String

    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\AARVIII\Documents\DATABASE\MP1.accdb"

Upvotes: 1

Views: 14060

Answers (3)

Steve
Steve

Reputation: 216363

Your connection string locates your database in a fixed position valid only on your PC.
A simple workaround is to use the |DataDirectory| substitution string. 

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + 
                       "Data Source=|DataDirectory|\MP1.accdb"

in this way, you can control the location of your database through code.
Usually (for a desktop application) the |DataDirectory| substitution string points the same folder where you have installed your application, but you need to have permission to write there and any kind of active database requires write permissions on its files. So this is not the best location for database files.

However you could change the location pointed by DataDirectory using code like this. (Of course put it BEFORE any attempt to talk to the database)

 ' Prepare a string pointing to a subfolder of the common application data 
 Dim appFolder = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData)
 Dim dbFolder = Path.Combine(appFolder, "MyAppFolder")

 ' Create the folder if it doesn't exist.
 Directory.CreateDirectory(dbFolder)

 ' Change the substitution string kept by DataDirectory
 AppDomain.CurrentDomain.SetData("DataDirectory", dbFolder)

Now the target directory for your database will be C:\programdata\myappfolder where your application has read/write permissions

More info on DataDirectory

Where is DataDirectory
DataDirectory where is documented

Upvotes: 4

nice.atma
nice.atma

Reputation: 38

I use this simple code and I can move the folder any where

conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\myDatabase.mdb"

Make sure to save the access file in the bin folder for this connection string to work.

Upvotes: 0

Dennis
Dennis

Reputation: 1547

Yea, you should use:

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "MP1.accdb"

And have the database file in the same folder as you startup .exe...

Upvotes: 1

Related Questions