locked
user set connection string RRS feed

  • Question

  • I am creating a program that will read MS Access Database information.  I have all of the forms created and everything work fine.  The only problem is that the databases are created by another application and in that application they have the ability to set the database in any location on the computer.

    What I would like to do is have the program check the default location of the database and if it doesn't find the databases, then I would like to give the user the ability to search for the database on their computer and or network. 

    I would appreciate any suggestions on how I should go about doing this.  Thanks in advanced.

    Tuesday, June 13, 2006 4:39 PM

Answers

  •  anubisascends wrote:

    Try

    Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo)

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    MessageBox.Show("Please locate the PSNC.MDB")

    Dim constr As String

    My.Forms.Options.OpenFileDialog1.Filter = "PSNC.MDB (PSNC.MDB)|PSNC.MDB"

    My.Forms.Options.OpenFileDialog1.ShowDialog()

    constr = My.Forms.Options.OpenFileDialog1.FileName

    Me.DBInfoTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB4.0;Data Source=" & constr

    Finally

    Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo)

     

    Ok, I have refined it a little, but now it tells me that the The 'Microsoft.Jet.OLEDB4.0' provider is not registered on the local machine.  How do I register the provider.  I can't find any documentation that tells me how to.  (I do know that doesn't mean it doesn't exist, I just can't find it)

    Again, thank you for any help.

     

     

    Ok, I figured this one out, if you look carefully, the Me.DBInfoTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB4.0;Data Source=" & constr was written incorrectly, there is supposed to be a . between OLEDB and 4.0, after I place the period in the correct place, it works like a charm.

    Thanks to all for your help.

    Sunday, June 25, 2006 7:43 PM

All replies

  • If the program can't find the database, prompt the user with an OpenFileDialog.  Once they pick the database, insert that value into your ConnectionString.  After that pass your ConnectionString object to an OleDBConnection, call .Open(), and you're ready to go.

    Hope that helps,

    Jonathan Aneja

    The VB Team

    Tuesday, June 13, 2006 4:44 PM
  • That's exactly how I would like to do it, but how?  I have searched the forums and haven't been able to find anything.
    Tuesday, June 13, 2006 11:01 PM
  • ok, I am trying to do this in one of the forms form_load event handler:

    My.Settings.psncConnectionString = OpenFileDialog1.FileName

    but this only gives me a "psncConnectionString is Read Only"

    Tuesday, June 13, 2006 11:17 PM
  • You can use the OleDbConnectionStringBuilder class to build your connection string.

    http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbconnectionstringbuilder.aspx

    Basically you want to set the "Data Source" part of your Connection String equal to the file the user picks (i.e. OpenFileDialog1.FileName).

    Once you've built your ConnectionString, you just pass it to an OleDbConnection (http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbconnection(VS.80).aspx)

    Tuesday, June 13, 2006 11:27 PM
  • Ok, I have read the information on these sites so here is the code up to the point that I get stuck:

    Try

    Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo)

    Catch ex As Exception

    MessageBox.Show(ex.Message & "Please select the location")

    Dim constr As String = ""

    My.Forms.StartPage.OpenFileDialog1.Filter = "PSNC.MDB (PSNC.MDB)|PSNC.MDB"

    My.Forms.StartPage.OpenFileDialog1.ShowDialog()

    constr = My.Forms.StartPage.OpenFileDialog1.Filter

    Dim command As New OleDb.OleDbCommand()

    command.Connection = New OleDb.OleDbConnection _

    ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & constr)

    Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo)

    End Try

     

    the final Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo) throws an exception when it can't find the file (of course).  So, the question is now, how do I take the information In this code and apply it the the me.psncdataset.dbinfo?

    Thursday, June 15, 2006 12:50 AM
  • Try

    Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo)

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    MessageBox.Show("Please locate the PSNC.MDB")

    Dim constr As String

    My.Forms.Options.OpenFileDialog1.Filter = "PSNC.MDB (PSNC.MDB)|PSNC.MDB"

    My.Forms.Options.OpenFileDialog1.ShowDialog()

    constr = My.Forms.Options.OpenFileDialog1.FileName

    Me.DBInfoTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB4.0;Data Source=" & constr

    Finally

    Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo)

     

    Ok, I have refined it a little, but now it tells me that the The 'Microsoft.Jet.OLEDB4.0' provider is not registered on the local machine.  How do I register the provider.  I can't find any documentation that tells me how to.  (I do know that doesn't mean it doesn't exist, I just can't find it)

    Again, thank you for any help.

     

     

    Sunday, June 25, 2006 3:22 AM
  •  anubisascends wrote:

    Try

    Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo)

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    MessageBox.Show("Please locate the PSNC.MDB")

    Dim constr As String

    My.Forms.Options.OpenFileDialog1.Filter = "PSNC.MDB (PSNC.MDB)|PSNC.MDB"

    My.Forms.Options.OpenFileDialog1.ShowDialog()

    constr = My.Forms.Options.OpenFileDialog1.FileName

    Me.DBInfoTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB4.0;Data Source=" & constr

    Finally

    Me.DBInfoTableAdapter.Fill(Me.PsncDataSet.DBInfo)

     

    Ok, I have refined it a little, but now it tells me that the The 'Microsoft.Jet.OLEDB4.0' provider is not registered on the local machine.  How do I register the provider.  I can't find any documentation that tells me how to.  (I do know that doesn't mean it doesn't exist, I just can't find it)

    Again, thank you for any help.

     

     

    Ok, I figured this one out, if you look carefully, the Me.DBInfoTableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB4.0;Data Source=" & constr was written incorrectly, there is supposed to be a . between OLEDB and 4.0, after I place the period in the correct place, it works like a charm.

    Thanks to all for your help.

    Sunday, June 25, 2006 7:43 PM