prompt for users to browse database connection if current database connection fails

Question

I am building a data-entry program in vb.net that 5 people will share using, and I have problems setting the right database connection. It would do basic things like: pulling up the stock units, save job, load jobs operations.

The database I'm using is Access database (.mdb). This database will be located in a local server drive (mine is in Z drive) and the connection string looks like this

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="Z:\Jimmy's Files\Quality Enclosures.mdb"

This works fine on my computer, but the problem is it does not work on my coworkers computer.

d (\dc-qenclosures) (Z:) is the loacation to my local server drive, but on my coworker's computer, it is set up as d (\dc-qenclosures) (Q:).

So, whenever I open the program on my co-worker's computer, it gives me an error saying that the database Provider=Microsoft.Jet.OLEDB.4.0;Data Source="Z:\Jimmy's Files\Quality Enclosures.mdb" does not exist (which makes sense because it is not under Z: on his computer)

I know how to use the OpenFileDialog to browse for mbd files.. but how do I set this as the new database connection? I want to create a properties menu to set the database location.

This is the code I have so far to browse for the database file

Private Sub RadButton6_Click(sender As Object, e As EventArgs) Handles RadButton6.Click

    Dim myStream As Stream = Nothing
    Dim openFileDialog1 As New OpenFileDialog()

    openFileDialog1.InitialDirectory = "c:\"
    openFileDialog1.Filter = "mdb files (*.mdb)|*.mdb|All files (*.*)|*.*"
    openFileDialog1.FilterIndex = 1
    openFileDialog1.RestoreDirectory = True

    If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
        Try
            myStream = openFileDialog1.OpenFile()
            If (myStream IsNot Nothing) Then
                myConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & openFileDialog1.FileName
                con.ConnectionString = myConString
                con.Open()
            End If
        Catch Ex As Exception
            MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
        Finally
            ' Check this again, since we need to make sure we didn't throw an exception on open. 

            If (myStream IsNot Nothing) Then
                myStream.Close()
            End If
        End Try
    End If
End Sub

Or, if over a network(same domain), put it in a shared folder and use a UNC path. — OneFineDay, May 01 '15 at 17:38
Thank you! I am very new to Vb.net, so I didn't know I could do that. Thank you for the right direction! — Jimmy Sungmin Park, May 02 '15 at 11:54

score 0 · Accepted Answer · answered May 01 '15 at 19:21

I'd second @OneFineDay's suggestion of trying the UNC path first, but to answer your main question of browsing for and saving a database path, you may want to look at storing elements of your connection string in My.Settings, so you could make the path a user-level setting. There's an MSDN article with examples here: Using My.Settings in Visual Basic 2005

In short, you'd have the code sample you shared save the value to My.Settings.DataPath (or whatever you decide to call the setting). Then when you're connecting to the database and need the connection string, you'd make it read from My.Settings.DataPath.

Of course, that'd store your path to the database in a plain-text app.config file by default, so you'll want to be conscious of that and take appropriate steps if there are any security concerns around your app or database.

Thanks, A.Franklin, exactly what I needed. I needed to know how to store as settings as well as know how to set UNC path. Thanks for the help! — Jimmy Sungmin Park, May 02 '15 at 11:57

prompt for users to browse database connection if current database connection fails

1 Answers1