Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    75

    Unanswered: Change Pass-Through Query Connection Dynamically

    Hello!

    I have a linked table db (DAO) in which I am using pass-through queries. When the db is moved from local to development and then to test and production, I need to point it to the right server by manually changing the connection string in VBA code (I don't store the connection string in the queries' "ODBC Connect Str" property not to let advanced users see the connection string.

    Is there a way to automatically redirect these queries based on the connection string of the db itself? Something like:

    qdf.connect=currentdb().connection so that the qdf could automatically be run against the server the tables are linked to?

    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The CurrentDb is a DAO Jet database while the Connection property is only accessible with a database in an ODBCDirect workspace. You cannot reference this property for the CurrentDb object (Run-time error 3251: Operation is not supported for this type of object.). The Connect property of a Database object is a zero-lenght string ("") when its base tables are in a Jet database (.mdb), which is the case of the CurrentDb object.

    You can retrieve the elements of the connection string from the Connect property of a linked table (by the way, your defence against advanced users is useless: what they cannot see from a pass-through query, they can from a linked table).
    Code:
    Function ConnectQuery()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("Tbl_LCF_Data")
        Set qdf = dbs.CreateQueryDef
        '
        ' Retrieve connection from a linked table.
        '
        qdf.Connect = tdf.Connect
        '
        qdf.SQL = "SELECT * FROM CF_Data"
        qdf.Name = "Qry_CF_Data"
        dbs.QueryDefs.Append qdf
        
    End Function
    You can also create a custom property on the CurrentDb object and use it to store the connection string for the queries.
    Have a nice day!

  3. #3
    Join Date
    Apr 2002
    Posts
    75
    Thanks a lot!!!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    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
  •