Access DB Path

[Tony K]

How can I customize, at runtime, the path for the OLEDB connection uses. I
used the Add Data Source Wizard and found the path set by the wizard is
ReadOnly.

 

[Guest]

Normally when you use the designer you use the Special data directory to
store your database ( it then becomes dynamic as the app will always look
to the data directory regardless where it is installed )
you can recognize this by the |Data Directory| in your connection string, it
means that you chose to copy the mdb file into your project when you use the
designer to generate the connection strings.

If you want to modify it, please do not change it in the setting tab of
project property.You can achieve that by "Right Click" your table adapter,
choose "Configure", and with two clicks of previous, you can change the
connection string.

Another option would be to concenate your own connection string , and do
everything from code ( loose the wizards )

regards

Michel

 

[Tony K]

Thank you Michel. I'm too far into my project to re-write that much code
(9 different forms that use that connection string). I'll have to get the
path from my customer and just recompile with that path for the connection
string and make sure the Access DB is in that path before they use the app.

Thank you again,

Tony K

 

[Guest]

i had just some spare time to dig some deeper


so this should then be your solution

'set a reference to system.configuaration
'set the following imports stratement above your class
'Imports System.Configuration

' Get the application configuration file.
Dim config _
As System.Configuration.Configuration = _
ConfigurationManager.OpenExeConfiguration( _
ConfigurationUserLevel.None)


'the full connection string name. normally designer generated is
assemblyname
'.My.MySettings.connectionName
'below is a sample how i used it with my test app
Dim csName As String = "TestRptWO.My.MySettings.DbCon"


' Create a connection string element
'( connection string how you want it i tested it with sql server but
it should work for anny provider )
Dim csSettings _
As New ConnectionStringSettings( _
csName, _
"LocalSqlServer: data source=127.0.0.1;Integrated Security=SSPI;" + _
"Initial Catalog=aspnetdb", "System.Data.SqlClient")

' Get the connection strings section.
Dim csSection _
As ConnectionStringsSection = _
config.ConnectionStrings

'first remove the old section from the collection
csSection.ConnectionStrings.Remove(csName)
' Add the new element.
csSection.ConnectionStrings.Add(csSettings)

' Save the configuration file.
config.Save(ConfigurationSaveMode.Modified)


with the above code you can change , remove or add connection strings
generated by the designer or even add your own custom ones through code

regards

Michel Posseth

 

[Phil]

How can I customize, at runtime, the path for the OLEDB connection uses.
I used the Add Data Source Wizard and found the path set by the wizard is
ReadOnly.

The Database property of the OleDBConnection object is readonly, but you can
change the ConnectionString which contains the database filename:

MyTableAdapter.Connection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDatabaseFilename

Put this in your form_load event before the database is opened.

There is also a ChangeDatabase method which looks promising, but for some
bizarre reason this only works after the database has been opened.

 

[Tony K]

Thanks again. I will try this when I get home today.


Tony K

 

[Tony K]

Phil,
Thank you for the info. I now have a couple things to try later today.

Tony K

 

[Tony K]

Michel,
Thank you very much. That was cool of you to spend the time digging for
my problem. It worked perfectly with my Access DB and also using the
OpenFileDialog to search for the file.

I'll post my code here:
This was inside the button_click event.

Dim config As System.Configuration.Configuration =
ConfigurationManager.OpenExeConfiguration( _
ConfigurationUserLevel.None)
Dim csName As String =
"Inventory.My.MySettings.Inventory_management_databaseConnectionString"

'Search for filepath to *.mdb Access file
Dim path As String
OpenFileDialog1.ShowDialog()
path = Me.OpenFileDialog1.FileName
Me.DBPathTextBox.Text = path

'Create a connection string element
Dim csSettings As New ConnectionStringSettings(csName,
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.DBPathTextBox.Text)

'Get the connection strings section
Dim csSection As ConnectionStringsSection = config.ConnectionStrings

'first remove the old section from the collection
csSection.ConnectionStrings.Remove(csName)

'Add the new element
csSection.ConnectionStrings.Add(csSettings)

'Save the configuration file
config.Save()

Tony K