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

Question

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"

I believe you are trying to say that you want your database connection string to be dynamic. So when you move your app folder the project can stil find you database. Is that correct? — Bart Teunissen
Have you tried a Data Source of .\MP1.accdb? Per stackoverflow.com/questions/5001980/…, relative paths are valid. — user565869

Steve Steve · Accepted Answer · 2013-08-01T14:40:59

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

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

3 Answers