By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,903 Members | 1,410 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,903 IT Pros & Developers. It's quick & easy.

Access DB Path

P: n/a
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.

Jul 29 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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" wrote:
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.
Jul 30 '07 #2

P: n/a
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
"Michel Posseth [MCP]" <Mi**************@discussions.microsoft.comwrote in
message news:AF**********************************@microsof t.com...
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" wrote:
>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.
Jul 30 '07 #3

P: n/a


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

"Tony K" wrote:
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
"Michel Posseth [MCP]" <Mi**************@discussions.microsoft.comwrote in
message news:AF**********************************@microsof t.com...
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" wrote:
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.
Jul 31 '07 #4

P: n/a

"Tony K" <no************@kingprogramming.comwrote in message
news:A5**********************************@microsof t.com...
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.
Jul 31 '07 #5

P: n/a
Thanks again. I will try this when I get home today.
Tony K
"Michel Posseth [MCP]" <Mi**************@discussions.microsoft.comwrote in
message news:48**********************************@microsof t.com...
>

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

"Tony K" wrote:
>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
"Michel Posseth [MCP]" <Mi**************@discussions.microsoft.comwrote
in
message news:AF**********************************@microsof t.com...
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" wrote:

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.
Jul 31 '07 #6

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

Tony K
"Phil" <N/Awrote in message
news:46**********************@ptn-nntp-reader02.plus.net...
>
"Tony K" <no************@kingprogramming.comwrote in message
news:A5**********************************@microsof t.com...
>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.
Jul 31 '07 #7

P: n/a
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_data baseConnectionString"

'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
"Michel Posseth [MCP]" <Mi**************@discussions.microsoft.comwrote in
message news:48**********************************@microsof t.com...
>

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

"Tony K" wrote:
>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
"Michel Posseth [MCP]" <Mi**************@discussions.microsoft.comwrote
in
message news:AF**********************************@microsof t.com...
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" wrote:

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.
Aug 2 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.