How do I copy an access database file with my appl

Question

In my Visual Basic Application, I have an access database file that I am using. It shows up in the solution explorer window. Everything works great until I install. The database file doesn't go with the installation for some reason. I guess I need to edit the connection string during runtime, but I am not sure. I have not done anything like this before, and I cannot find the information about it.

If someone could send me to a tutorial or give a brief explanation of how to use an access database once the application has been installed.

When my program runs, it creates a directory in

User\App Data\Roaming\CreatedFolder\Resources\DatabaseFile.accdb

So how do I set this path without knowing the full path up to App Data?

Answer 1

The way I would do this would be to:

1. Select Add New Data Source . . . from the Data Source window in Visual Studio
2. Select Database from the list that appears, and click next
3. Click next
4. In the Choose your data connection page of the wizard, click New connection
5. Choose Microsoft Access Database File from the listbox and click next
6. Choose a copy of the database file that is not in your directory and enter any login information
7. Click OK

8. Now Visual Studio will ask you:

   The connection you selected uses a local data file that is not in the current project. Would you like to copy the file to the project and and modify the connection?

   If you copy the data file to your project, it will be copied to the project's output directory every time you run the application. Press F1 for information on controlling this behavior.

Click "Yes," and Visual Studio will add the database to your project and make a connection string that points to the copied database.

9. Now you will probably want to save the connection string in App.Config so that you don't have to rebuild it every time you want to use the database. Most likely this will save a connection string that uses |DataDirectory|. Modify the contents of this page as you see fit.
10. Procede through the rest of the wizard and configure your database as you need it.

The wizard should configure the connection string and build information such that your application will work no matter where you take it.

EDIT - My connection string as it is saved in App.config looks like

    <connectionStrings>
        <add name="SOAccessDatabase.My.MySettings.Students_2000formatConnectionString"
            connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Students_2000format.mdb"
            providerName="System.Data.OleDb" />
    </connectionStrings>

I also have the database file's Build Action set to "Copy Always."

Answer 2

You can use

Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)

or

Environment.GetEnvironmentVariable("APPDATA")

They both should return something similar to

C:\Users\Gord\AppData\Roaming

so you can build your connection string like this:

Dim dbPath = _
        Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & _
        "\CreatedFolder\Resources\DatabaseFile.accdb"
Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath