SQL Database location

Question

In Visual Basic 2010 Express, I use SQL statements to read, write, edit, ... a mdb database file. However, at the moment, it's pointing to a location on my local directory.

Is there a way to embed the file into the VB program and change the SQL statement to write to it?

Sort of like how in HTML, you can move the whole website folder and so long, as the root contains the folder "images" for example, then it knows to look in there...

score 1 · Answer 1 · answered May 11 '13 at 15:02

Your reference to your MDB file will likely be contained in your app.config file as an SQL connection string. In there you'll find the full path to the file itself. If you change that to a relative path (say, just the name of the MDB file itself, no folders or anything like that) then it should look in the directory of the executable.

So, if your connection string was:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder1\mydatabase.mdb;User Id=admin;Password=;

You'd change it to:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydatabase.mdb;User Id=admin;Password=;

score 1 · Accepted Answer · answered May 11 '13 at 15:05

I don't know how you are currently stating the connection string for each of your sql statements, but one approach that you could take would be to place the .mdb file in the root folder of your application and then use the generic

Application.Info.DirectoryPath

to provide the basic location of the mdb file. A better idea however (especially to avoid problems with with the UAC and permissions would be to place the mdb file in the all users application data folder and use the equally generic pointer

Application.CommonAppDataPath

With some judicious experimentation you should be able to arrive at a solution that best meets with your own requirements.

score 0 · Answer 3 · edited May 23 '17 at 11:44

No you can't use an Access database as embedded as a resource.

You could set the connection string like this

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\myDatabaseFile.mdb;Persist Security Info=False;

and then in your startup code you could set the substitution for the DataDirectory with a call to

 Dim commonAppData = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData)
 AppDomain.CurrentDomain.SetData("DataDirectory", commonAppData)

Lacking this call then your DataDirectory predefined value will be the current application startup folder.

Some info on |DataDirectory| sustitution string

SQL Database location

3 Answers3