VB 2005 Access Connection string -Please Help

[Steve402]

I used the wizard in VB 2005 to add my database.

Under the "Applications Settings" area in VB my string is currently set to:

Name: MyDBConnectionString
Type: (Connection String)
Scope: Application
Value: Provider=Microsoft.Jet.OLEDB.4.0;Data Source="MyDB.mdb"

That works fine, it uses the database that is located in the apps startup directory... but I'd like to relocate the database to the "Application Data" folder instead.

I've been trying to figure this out for days, I've found lots of examples but they don't work, maybe because the "Applications Settings" can't use variables?

here is one of the many examples I've tried...

Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "& Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) & "\MyDB.mdb"

Any ideas? other than totally rewriting the app?

[jmcilhinney]

I would suggest using the |DataDirectory| place-holder in your connection string and then setting the location of your data directory at app startup. That way, there's no need to edit your connection string in code every time you want to make a connection. So, your connection string would be:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\MyDB.mdb

Now you need to open the Application page of the project properties and click the Application Events button, then use the drop-downs at the top of the code window to create a handler for the Startup event. That event handler should look like this:

Namespace My

    ' The following events are available for MyApplication:
    ' 
    ' Startup: Raised when the application starts, before the startup form is created.
    ' Shutdown: Raised after all application forms are closed.  This event is not raised if the application terminates abnormally.
    ' UnhandledException: Raised if the application encounters an unhandled exception.
    ' StartupNextInstance: Raised when launching a single-instance application and the application is already active. 
    ' NetworkAvailabilityChanged: Raised when the network connection is connected or disconnected.
    Partial Friend Class MyApplication

        Private Sub MyApplication_Startup(sender As Object, e As Microsoft.VisualBasic.ApplicationServices.StartupEventArgs) Handles Me.Startup
            Dim path = IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
                                       Windows.Forms.Application.CompanyName & "\" & Windows.Forms.Application.ProductName)

            AppDomain.CurrentDomain.SetData("DataDirectory", path)
        End Sub

    End Class


End Namespace

Notice that I used the company and product names of your app in the path, which is convention. Don't just chuck files into the application data folder. Put them in a place specific to your application.

[Steve402]

You're right, I do want the company and product name in the path, I only left that out because I was trying to narrow down what I was doing wrong.

You'e solution worked perfectly! and thanks for the detailed instructions, that is a huge help because I have no formal VB training.

Thank You!!!