Geek Answers Handbook

Cross reference to multiple databases

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 a SQL Server database based on data in an Access database. What is the best way to accomplish this using ADO.NET?

Is it possible to pull the necessary data from each database into two new tables. Put them in one dataset. Then run another SELECT command in the dataset to combine the data?

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

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

My decision

In the end, I just imported the data in the access db into a temporary table on the db SQL server. Performing all necessary processing, deleting the temporary table. It was not a pretty solution, but it worked.

+5
source share
6 answers

, . Tomalak, .

, Access / , , . Access, , , SQL Server. Access, SQL Server, , .

, :

  • , , , , ADO.NET.
  • , , .
+3

, Sql Server Access, OPENROWSET.

 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

Access , MDB.

+1

, , Access SQL Server ODBC SQL, ? , , Jet . , Jet . (, , SQL Server ), , Jet . SQL, ( IN SELECT... WHERE).

, , , WHERE. , , Jet SQL Server , , , , , Jet , SQL Server.

, , ( , ). , , , , Jet . , Jet , , , , Access .

0

, .

FILE, :

, ( ), , , , ...

, .

, , , ​​ LDAP.

LDAP, , GUID, , .

0

, .

, :

  • SQL Server
  • SQL Server Access , .
  • sql, where

, ADO, Access , , datatable. ADO SQL Server Where, .

Other solutions that people give suggest that you need to make connections to your data or otherwise execute SQL that includes both databases. To do this, you need to use linked databases or import data into a table (possibly temporary).

0
source

More articles:


All Articles