0

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;

  • Being able to build a query using Sql DB and Access DB visually by dragging and dropping tables.
  • Being able to save these queries and re-run them using C#
  • Being able to use sub-queries (query in another query)

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?

Improve this question
4
  • 3
    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 Commented Nov 11, 2016 at 16:00
  • @KieranQuinn Can SQL Server Management Studio bring all the tables from a MS Access Database in its own query builder ? By the way, it cannot be always slow. We have built a specific query by combining data inside a C# application from both DBs which runs very fast. All we need to do is building and saving queries easily. Commented Nov 11, 2016 at 16:14
  • It can but you need to import the tables from Access to SSMS database first. 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. Commented Nov 14, 2016 at 10:14
  • 1
    @KieranQuinn you were right, after getting some help from an expert in this field, we were able to do all of the listed requirements using SQL Server Management Studio. If you provide it as an answer I`ll choose it. Commented Nov 17, 2016 at 14:59

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

SQL Server Management Studio does all this that`s true, but I don't get the part why should I upload my data to temp tables in SQL. My tables are keep growing with new records, what you are suggesting is every time I need to use this query, I should recreate a temporary table of millions of records plus querying among the created tables. I don't get how this is faster than simply querying linked tables.
You can still use access, but look into using VBA to upload your access table to a temp table (create table #tableName.....) on the SQL server you want to query it against. Then you can run an SQL query against the SQL Server and join the temp table with the SQL server table. The temp table will only exist on the SQL server database while you have access open. Once you close it the table disappears. Does that make sense?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.