JOIN an Access table and a SQL Server table in a single query from C#

Question

I would like to use c# to select and display data from an SQL Server and a MS Access Database (accdb).

E.g.

SELECT sqlserverTable.id , sqlserverTable.data, accessTable.info 
FROM sqlserverTable 
INNER JOIN accessTable on accessTable.id =  sqlserverTable.id

Current approach to connect to the SQL Server in c# uses:

string connectionString = "Data Source=SQLDATA;Initial Catalog=P22;
    User ID=<user>;Password=<pw>";
SqlConnection cnn = new SqlConnection(connectionString);

and Access:

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=\\network\data\access.accdb";
OleDbConnection aCnn = new OleDbConnection(connectionString);

I have read I could use linked server (sp_addlinkedserver). But I do only have read access to the SQLServer. I would love to use a c# only solution.

Is there a way to run a Join from multiple data connections?

Best wishes, Hakuin

I tried OPENROWSET as suggested by @CrnaStena but only got as far as

cnn = new SqlConnection(connectionString);
cnn.Open();
SqlCommand command = new SqlCommand(" SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'D:\\access.accdb';'admin';'' ,  Tabelle1) As ors ");

And then trying to get around a 'Microsoft.ACE.OLEDB.12.0' not registered error. There are plenty of solutions for this recommending to install "Access Database Engine 2007/2010 in 32/64bit " and "Office Data Connectivity Components". But so far it did not resolve the error message. Also to compile in x86 mode only. So currently I'm stuck here.

Based on the other comments: The dataset is rather small. Less than 10.000 rows are affected and those in most cases reduced using where statments to display < 100 rows.

If I am understanding correctly, you want to get the data into your app, and join it there. Have a look at here: https://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp — Keith, Oct 27 '17 at 07:48
That's fine for small data sets. Do you really want to pull into memory 10 million rows or GB of data? Not that MS Access supports such large tables. — CrnaStena, Oct 27 '17 at 07:58
Are you running this code on the same machine the .accdb database is on? And is it the same machine running SQL server? — Erik A, Oct 27 '17 at 10:19

score 0 · Answer 1 · answered Oct 27 '17 at 07:53

0

I would look into OPENROWSET, here is documentation. Then you could do something like this:

USE Northwind  ;  
GO  
SELECT c.*, o.*  
FROM Northwind.dbo.Customers AS c   
   INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',   
   'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)      
   AS o   
   ON c.CustomerID = o.CustomerID ;  
GO

I know this in not with C#, but depending on your table size, I would not try to pull into C# 10 millions rows just to do join on another table of 10 million rows. So, if your tables have 50 rows great, otherwise look for DB solutions.

answered Oct 27 '17 at 07:53

CrnaStena

3,017
5
30
48

I got as far as SqlCommand command = new SqlCommand(" SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'D:\\access.accdb';'admin';'' , Tabelle1) As ors "); – Hakuin Oct 27 '17 at 09:15
I am not sure I am following. Did this help you resolve issue? – CrnaStena Oct 27 '17 at 09:17
Sorry, I thought I could write longer comments here. But now I did edit the main post. – Hakuin Oct 27 '17 at 09:27

score 0 · Answer 2 · answered Oct 27 '17 at 13:29

The Access Database Engine should be able to join both tables if you use the [ODBC; ...] syntax to reference the SQL Server table. I just tried the following and it worked for me:

connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\Database1.accdb";
using (var conn = new OleDbConnection(connStr))
{
    conn.Open();
    string sql = 
            @"SELECT " +
            @"    sqlserverTable.id, " +
            @"    accessTable.[Last Name] AS lname_a, " +
            @"    sqlserverTable.last_name AS lname_s " +
            @"FROM " +
            @"    Donor AS accessTable " +
            @"    INNER JOIN " +
            @"    [ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;DATABASE=myDb;Trusted_Connection=yes].Donor AS sqlserverTable " +
            @"        ON accessTable.[Donor ID] = sqlserverTable.id";
    using (var cmd = new OleDbCommand(sql, conn))
    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            Console.WriteLine("{0} | {1}", rdr["lname_a"], rdr["lname_s"]);
        }
    }
}

JOIN an Access table and a SQL Server table in a single query from C#

2 Answers2