Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    South Dakota

    Unanswered: Pass Through Query

    To All--

    I don't know if this is even possible. I have an Access database where I want to put a Pass Through Query. Thing is I would like the PTQ to use 2 different SQL servers. I would like to use a PTQ as a subquery in the original PTQ as part of the Where clause.

    Does that even make sense? Thanks.


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    It makes sense but you can't, at least not that way.

    What makes a query a pass-through query is it's Connect property. For a PTQ the Connect property is not empty (zero-length string) and contains a connection string, such as:
    ODBC;DRIVER={SQL Server};SERVER=SANDBOX;DATABASE=Sales;Trusted_Connection=Yes;
    As you can notice, the name of the server is present in this connection string and this name must be unique.

    I can see two possible solutions:

    1. Create 2 pass-through queries (one for each server) and use them in a third (local) query.

    a) On one of the SQL Servers ([Server_A]), create a Linked Server (the second one [ServerBB]):
    -- From Server_A
    USE master;
    EXEC sp_addlinkedserver 
       N'SQL Server'
    b) On [Server_A], create a stored procedure that can now use references to [Server_B].
    c) Call the stored procedure mentioned in (b) using a pass-through query in your Access database.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts