0

I am currently working on a query in Access 2010 and I am trying to get the below query to work. I have the connection string between my local DB and the server that I am passing through to working just fine.

Select column1 , column2 from serverDB.dbo.table1 where column1 in (Select column1 from tbl_Name1)

In this situation table1 is the table on the server that I am passing through to get to, but the tbl_Name1 is the table that is actually in my Access DB that I am trying to use to create constraints on the data that I am pulling from the server.

When I try to run the query, I am getting the error that it doesn't think tbl_Name1 exists.

Any help is appreciated!

tarheel
  • 4,727
  • 9
  • 39
  • 52

2 Answers2

1

I just came across a solution that may help others in a similar situation.

This approach is easy because you can just run one query on your local Access database and get everything you need all at once. However, a lot of filtering/churning-through-results may be done on your own local computer behind the scenes, as opposed to on the remote server, so it may not necessarily be quick.

Steps

  1. Create a query, make it a "Pass Through" query, and set up its "ODBC Connect Str" property to connect to the remote database.
  2. Write the pass through query, something like SELECT RemoteId From RemoteTable and give your pass through query a name, maybe PassThroughQuery
  3. Create a new query, make it a regular "Select" query.
  4. Write your new query, using the pass through query you just created as a table in this new query (seems weird to use a query as a table, but it works) and join that PassThroughQuery "table" to your local table and filter it based on values in the local table, something like SELECT R.RemoteId, L.LocalValue FROM PassThroughQuery R INNER JOIN LocalTable L ON L.LocalId = R.RemoteId where L.LocalValue = 'SomeText'

This approach allows you to mix/join the results of a pass through query and the data in a local Access database table cleanly, albeit potentially slowly if there is a lot of data involved.

0

I think the issue is that a pass through query is one that is run on the server. Since one of the tables is located on the local Access file, it won't find the table.

Possible workaround if you must stay with the pass-through is you can build an SQL string with the results of the nested query rather than the query string itself (depending on the number of results this may or may not be practical)

e.g. Instead of Select column1 from tbl_Name1 you use "c1result1","c1result2",....

ashareef
  • 1,846
  • 13
  • 19
  • 1
    If the tables are linked you'll be able to treat the tables as they are both local, but there may be a performance loss with the query – ashareef Jul 22 '14 at 15:24
  • Similar discussion http://stackoverflow.com/questions/23615708/sql-join-from-microsoft-access-table-to-another-table-through-odbc-using-pass-th – ashareef Jul 22 '14 at 15:26