changing the database runtime

[johmolan]

I have tried several times to find a way to change the database in runtime.

I know it can be done but I need some help finding out how.

I use an Access database lokated on my lokal machine but when I go to work on another computer I wish to be able to use an open file dialog to choose another databasefile.

Can someone please help me here?
A lilttle code snip would be most appreciated.

 

[Troy]

Hi this is what I use for it, and The .mhm extension is still an access format mdb I just renamed to associate the database and any created to work by default with my app. You can change .mhm back to .mdb and use it with any access db.

Hope it's what you were asking.

Private Sub FileOpen()

        Try
            With OpenFileDialog
                .FileName = ""
                .AddExtension = True
                .CheckFileExists = True
                .CheckPathExists = True
                '.InitialDirectory = My.Computer.FileSystem.GetParentPath(My.Settings.dbPath) /////Remarked out, just left in in case I want to reactivate it!
                .Filter = "MHManager Database|*.mhm"

                If .ShowDialog() = Windows.Forms.DialogResult.OK Then

                    My.Settings.dbPath = .FileName ' Not needed i just use settings to store the value if I want to use it later in another windows form.

                    'Open the Database               
                    OpenDatabase(.FileName)

                    Exit Sub
                End If
            End With
        Catch ex As Exception
            'Display an error to the user.
            MsgBox("CORRUPTED OR INVALID DATABASE!" & Chr(13) & Chr(13) & "An error occurred. Error Number: " & Err.Number & _
    " Description: " & Err.Description & " Source: " & Err.Source, MsgBoxStyle.Critical, "ERROR!")

        End Try

    End Sub

   Public Sub OpenDatabase(ByVal filename As String)
        Dim strConnection As String

        'Set the Connection String
        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" _
                                & filename & "; Persist Security Info=False"

        cn.ConnectionString = strConnection

        'Open the Connection
        cn.Open()

        ' Fill the Contacts DataTable 
        daContact.Fill(dsContact, "Contact")
        dtContact = dsContact.Tables.Item("Contact")

        ' Fill the Package DataTable
        daPackage.Fill(dsPackage, "Package")
        dtPackage = dsPackage.Tables.Item("Package")

        ' Fill the Service Table
        daService.Fill(dsService, "Service")
        dtService = dsService.Tables.Item("Service")

        'set Status of Database
        My.Settings.dbOpened = True

        'Close the Connection
        cn.Close()

 

[cjard]

Can someone please help me here?
A lilttle code snip would be most appreciated.

Please choose the result that is closest to what you require:

1) the user will choose which database to use, each time the program is started
2) the user will only change the database when needed, every time the program is started it will remember the previous choice

 

[johmolan]

the number 1 is the choice closest

 

[ggunter]

I'm still new to DotNet and I'm sure cjard will have a much more elegant answer but I would drop an OpenFileDialog object onto your form.

 

[cjard]

If your need is option 1, then open the partial class for the Settings and add a WriteOnly property that alters the ConnectionString (or whatever you called yours)

Now, when the user starts the app, ask them for the DB, make a conenctions stirng and set it using this property

i.e. something like:

Partial Class Settings

  WriteOnly Property ConnectionStringWr
    Set
      Me["ConnectionString"] = Value
    End Set
  End property
End Class

If you were going option 2, I'd have said change the COnnectionString to be a user scoped string, but that has pitfalls of its own