Changing the connection string of a DataSet


New member
Jan 6, 2011
Programming Experience
Hi All,

Let me begin by explaining you what i am doing.
In my appication i created a dataset and populating it using access database as datasource, and evrthyng is working fine. when i change the datasource i change the connection string in settings.vb and evything works fine as long as the new datasource is a access database.
what i need is to be able to change the data source type to an odbc connection.
and whenevr i do that it throws an error in the dataset .designer.vb saying that i need to specify provider, which in my case i dont need to since i am changing the connection to mysql server. Can i do that? and if yes how .

Thank in advance for your help
Typed DataSets are specific to a data source. If you want to change the data source then don't use a typed DataSet. You can write all your ADO.NET code yourself and use an ODBC driver for Access.

That said, why not just develop for MySQL in the first place? The database is free and so is the ADO.NET provider.
Thx for the reply

Yes i figured what u said earlier.

well i want the application to be flexible at the database level. in other words, i want the user to the location of the database either local (MS access) or from mysql server. and then i use this datasource to populate the dataset that are binded to my forms and reports.

i did some research about the in the msdn website. i think i am going to go with this solution, but still trying to figure out how to do it :s
If you want the application to be truly flexible then you need to create a generic data access layer and then plug in data-source-specific implementations as needed. You generally can't use a single data access layer for multiple data sources without change because there are usually going to small differences that will require some sort of change. If you use the Data Source wizard with an Access database then it's going to use OleDb under the hood and it will contain SQL code that will likely be incompatible with MySQL. You could try writing your own ADO.NET code using OleDb or Odbc and then ensure that the required provider or driver is present at run time but you may still not find it possible to write all your SQL to be compatible with both data sources.
I don't always agree.. one typed dataset can be used for multiple sources, regardless of how much hard work is there inherent..

To change the connection string at runtime you either add your own Set property to the code behind the settings file (look at the designer generated code for the Get property, and write an equivalent set in the partial class) - this lets you edit the value of an application scope setting. it will not be saveable unless you persist it yourself between app runs

Or you can change the connectionstring to be a User scope string instead of an application scope connection string.. it will still work in runtime and be saveable with my.settings.ave but the dataset designer cannot use it and will throw errors all over the place. be creful to change it back to application scope/conn str before making any changes to your dataset