I want a query that gets data from 2nd DB

[qwert231]

K, here's the deal. On my SQL database I have 2 tables. I want to get data something like this:
SQL db Stored Procedure (since I can't do a view with a query.)

SELECT sqltable1.Field1, sqltable1.Field2, sqltable1.Field3
 FROM sqltable1 INNER JOIN sqltable2 ON sqltable1.Field1 = sqltable2.Field1
 WHERE sqltable2.Field2 = @Variable

Access table:

SELECT accTable1.Field1, accTable1.Field2, accTable1.Field3, sqlQuery.Field3
 FROM sqlQuery INNER JOIN accTable1 ON (sqlQuery.Field1 = accTable.Field4) AND (sqlQuery.Field2 = accTable.Field5)
 WHERE accTable.Field6 = ?Variable

The variable in both queries is the same value. The customer ID value. The SQL query is pretty big, so I would like it to do the work on the SQL server, not the access DB. I had tried it with a Link table in the ACCESS table and building the query in ACCESS, which works, but is very slow! I would rather the ACCESS DB do as little work as possible. But the ACCESS tables are the real Parent here. Any ideas? Help?

 

[jrbarnett]

Have you tried doing it as a pass through query. This will be stored in the access database, but executed on the SQL server and the results returned to Access.

John

 

[carmo]

It is very slow because Access is pulling all the data from the tables in the query, then doing the SELECT in access. You need to use a Pass-Through query. A passthrough query will be an SQL query that will be processed by the SQL database rather than Access. Therefore, only the selected records will be uploaded to access. This will greatly increase performance.

 

[qwert231]

That sounds great, but I have never done a pass through before. I will look for info, but if you have the time, could you drop me a line and point me in the right direction?

 

[jrbarnett]

Hi,

Basic instructions for a pass through query:

1. In access, go to queries and new query.
2. Press escape to bypass the wizard, then SQL to go to SQL view.
3. Type your SQL code into the box.
4. Go to Query menu, choose SQL Specific and select Pass Through query (this is Access 2002 instructions; other versions may differ).
5. Save the query.

Note that this has to be written in SQL Server SQL, rather than Jet, so Access won't be able to perform a syntax check on the code before it executes it.

John