CODEWITHSUNDEEP
c#sql-serverms-accessdatabase-connectionquery-builder
0014
0014

Reputation: 933

Building Queries Using Access and Sql Database

I am working in a company where we have hundreds of tables and queries in MS Access and several tables in our SQL Server. Sometimes we need to combine results from both databases.

When we try to do it by linking our SQL tables to MS Access using ODBC connection, the results return very slow that causes our clients to complain. So other than non-ODBC connection property here is what I am looking for;

My question is if there is any kind of application or API (paid or unpaid) that supports all these terms? Or is it possible to build such a thing?

Upvotes: 0

Views: 76

Answers (1)

Kieran Quinn
Kieran Quinn

Reputation: 1115

SQL Server Management Studio does all this. But running a query by trying to join a SQL server table with an access table is not a good idea and will always be slow. You need to upload the access table data to a temporary table in SQL Server and join from there.

You can use vba to import the record set to a temp table and then do a join. If you're drawing down both datasets in C# app and joining there then yes that will be relatively more efficient than joining in Access but certainly not the same thing. Joining a large Access table with another large linked table will always be slow.

Upvotes: 1

Related Questions