Cross-referencing across multiple databases

Question

I have two databases, one is an MS Access file, the other is a SQL Server database. I need to create a SELECT command that filters data from the SQL Server database based on the data in the Access database. What is the best way to accomplish this with ADO.NET?

Can I pull the required data from each database into two new tables. Put these in a single Dataset. Then perform another SELECT command on the Dataset to combine the data?

Additional Information: The Access database is not permanent. The Access file to use is set at runtime by the user.

Here's a bit of background information to explain why there are two databases. My company uses a CAD program to design buildings. The program stores materials used in the CAD model in an Access database. There is one file for each model. I am writing a program that will generate costing information for each model. This is based on current material prices stored in a SQL Server database.

---

My Solution

I ended up just importing the data in the access db into a temporary table in the SQL server db. Performing all the necessary processing then removing the temporary table. It wasn't a pretty solution but it worked.

Answer 1

You don't want to pull both datasets across if you don't have to do that. You are also going to have trouble implementing Tomalak's solution since the file location may change and might not even be readily available to the server itself.

My guess is that your users set up an Access database with the people/products or whatever that they are interested in working with and that's why you need to select across the two databases. If that's the case, the Access table is probably smaller than the SQL Server table(s). Your best bet is to pull in the Access data, then use that to generate a filtered query to SQL Server so that you can minimize the data that is sent over the network.

So, the most important things are:

1. Filter the data ON THE SERVER so that you can minimize network traffic and also because the database is going to be faster at filtering than ADO.NET
2. If you have to choose a dataset to pull into your application, pull in the smaller dataset and then use that to filter the other table.

Answer 2

Assuming Sql Server can get to the Access databases, you could construct an OPENROWSET query across them.

 SELECT a.* 
 FROM SqlTable 
 JOIN OPENROWSET(
     'Microsoft.Jet.OLEDB.4.0', 
     'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
     Orders
 ) as b ON
     a.Id = b.Id

You would just change the path to the Access database at runtime to get to different MDBs.