locked
How to dynamically change the pass through query environment RRS feed

  • Question

  • Hi all,

    I am running Access 2007.  I have a number of pass through queries I created.  During development when the queries were created, they were pointing to the development environment.  But what I would like to do is to change the environment it is pointing to based on a value in a table, whether it is: Dev, UAT or Prod.  I see the value in property sheet of the query in "ODBC Connect Str". 

     

    Can you share with me how I can change this value dynamically to allow it to point to the right DSN based on the value in the table?  I have a DSN for Dev, UAT and Prod.

     

    Thank in advance,

     

    Gerry


    Wednesday, August 31, 2011 1:29 PM

Answers

  • The simple approach: reuse a connection string from a table pointing to the correct database.

    CurrentDb.QueryDefs("PassthroughQuery").Connect = _
      CurrentDb.TableDefs("LinkedTable").Connect

    Otherwise I would change the connection strings entirely to a DSN-less one.

    • Marked as answer by GerryStilton Wednesday, August 31, 2011 3:18 PM
    Wednesday, August 31, 2011 1:46 PM

All replies

  • The simple approach: reuse a connection string from a table pointing to the correct database.

    CurrentDb.QueryDefs("PassthroughQuery").Connect = _
      CurrentDb.TableDefs("LinkedTable").Connect

    Otherwise I would change the connection strings entirely to a DSN-less one.

    • Marked as answer by GerryStilton Wednesday, August 31, 2011 3:18 PM
    Wednesday, August 31, 2011 1:46 PM
  • Thanks Stefan.  That works.

     

    Gerry

     


    NoSpam007
    Wednesday, August 31, 2011 3:18 PM