Relations between tables from different db

[nicorvp]

Hello,
using vb2005 I have loaded a new datasource - database - access file and in the .xsd file I can see my tables. Then, on the same dataset, I added some other tables from another access file using add TableAdapter.
Now if I use the (trying to translate from italian) "Configuring dataset with wizard" I can see only tables from the second access db.
How to configure also the first db with the wizard? Where does the wizard read the db to use?
Orelse
what i have to do is to link tables from different access db with relations. What is the best way?
Thanks

 

[jmcilhinney]

A typed DataSet can only have one connection string, i.e. it can only connect to one database. If you want to have data from multiple databases in one typed DataSet then you have to connect to one database from the DataSet and then connect to the second database from the first database. I know you can link multiple Access databases but I've never done it myself so I can't tell you how. It's done in Access anyway so it's not really an ADO.NET issue.

 

[jmcilhinney]

The alternative would be to use an untyped DataSet, in which case you can create DataAdapters with as many different connection strings as you like.

Actually, I guess you could still use a typed DataSet and just set the connection string for some of the TableAdapters in code.

 

[Tom]

A typed dataset might only allow a single connection at a time but I'm wondering what would prevent him from creating his typed dataset tables and then through coding connect to the first db, fill those tables and then create a second connection after closing the first and filling the remaining tables from the second db?

I have used Linked Servers in Sql Server to allow me to connect to multiple Sql Server & Access databases at a time. I dont know if this is an option in just Access though.

 

[jmcilhinney]

A typed dataset might only allow a single connection at a time but I'm wondering what would prevent him from creating his typed dataset tables and then through coding connect to the first db, fill those tables and then create a second connection after closing the first and filling the remaining tables from the second db?

Yeah, as I said in my second post, you can set the TableAdapter.Connection.ConnectionString for each TableAdapter object. You need to configure the DataSet to expose the connection publicly first. Also, it must be done for each and every TableAdapter you create that won't use the default connection string.

 

[cjard]

A typed DataSet can only have one connection string, i.e. it can only connect to one database.

I disagree. I have several DataSets having TableAdapters that have distinct CSs

One tableadapter can only have one Connection String, it's true, but the CS is not part of the DS itself per se. Each TA in a DS can have its own CS

If I open a DS and get properties on the surface (not the file) there is NO CS for the DS. To see CS we have to click a TA. When making a new TA we are offered the option to use an existing or new CS. All CSs in use are visible in the xml behind the surface (open in xml editor) but they are not shown in the surface itself; each TA has the option to alter its CS. DataSets do not have connection modifier properties. Further, I have some TAs that don't have a CS of their own; every command they hold has its own CS. These TAs are manifest by choosing New..Query.. from the DS surface context menu

 

[cjard]

Actually, jmc's posts would make a lot more sense if the world "TableAdapter" were substituted in place of "DataSet"