Two different databases

[pachjo]

Can someone give me some pointers as to how I gather data from two different databases please?

I have an mdf file which will be on a file server and have connected to it in server explorer and created a datasource from it with the dataset and relevant tableadapters.

I also have to connect to a series of tables in a database on an sql server.

What I need to do is run a query that will gather data from the required table on the sql server database and append it to the mdf file database on the file server.

In the mdf file tableadpter fill query it says it does not recognise the sql server database table as it see no link to it?

How do I do this?

Thx

 

[Stonkie]

You should be doing this by building two strongly typed datasets, loading data from the SQL Server database into the DataTable of its own dataset, use some code to go through the DataTable and create the same data in the DataTable of the dataset related to the Access database and do an update of the lot.

That would probably be the easiest to code (within a 50 lines of code if you don't have to do data processing) and unless performance is a consideration, it should be fine.

Now if this is to be run multiple times and you just want to import the data, you may want to use the SqlDataReader directly and avoid using a DataTable for data that won't be used later on or use some other optimization technique to make better use of memory and processing time.

 

[jmcilhinney]

While it's one approach, you don't need typed DataSets here and you don't need two DataTables with code to move data from one to the other. One DataTable is all you need because a DataAdapter doesn't care where a DataTable came from.

Dim table As New DataTable

adapter1.Fill(table)
adapter2.Update(table)

That's it. The first DataAdapter needs an appropriate SelectCommand and the second DataAdapter needs an appropriate InsertCommand. You also need to set the AcceptChangesDuringFill property of the first DataAdapter to False. That way the RowState of each DataRow will be Added rather than Unchanged and they will not be ignored by the second DataAdapter.

 

[Stonkie]

I am not so sure SQL Server and Access datatypes will translate to the same DataTable configuration, but I must admit I never tried it and I don't know what will happen with your primary key... It's worth the try though as it's just 2 lines long

 

[jmcilhinney]

I am not so sure SQL Server and Access datatypes will translate to the same DataTable configuration, but I must admit I never tried it and I don't know what will happen with your primary key... It's worth the try though as it's just 2 lines long

Of course they will. A DataTable doesn't contain SQL Server types or Access types. It contains .NET types. If you have a text column in any database at all then populating a DataTable will create a String object in each DataRow. When you save that DataTable the ADO.NET provider will translate the data from the .NET String type to the appropriate type for the database it's saving to. That's what ADO.NET providers are for: to provide a translation layer between the common ADO.NET interface and the specific interface of the data source they were written for.

 

[cjard]

Can someone give me some pointers as to how I gather data from two different databases please?

Actually, you can configure one of you sql servers to connect to the other one, or you can attach both MDF files to the same sql server instance and conenct as a user that has server wide privs