Results 1 to 3 of 3
  1. #1
    uoghk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    149

    create dynamic query pass through

    I create a Pass-Through Query name "qryPT", and set its ODBC connect string as


    "ODBC;DRIVER=SQL Server;SERVER=luenkeesql;UID=sa;PWD=sqladmin;DATAB ASE=LuenKeeSystem" in the Property Window.

    It works well on below coding.

    Code:
    QueryServer "select * from customer"
    
    Sub QueryServer(SQLStatement As String)
        Dim db As DAO.Database
        Dim qDef As QueryDef
        
        Set db = CurrentDb()
        Set qDef = db.QueryDefs("qryPT")
        qDef.sql = SQLStatement
        
        Set qDef = Nothing
        Set db = Nothing
    End Sub
    But how can I put the connect string inside the Sub QueryServer?
    So that I don't need to recreate the Pass-Through Query "qryPT"

    Thanks

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Use the connection string from an existing linked table, something like
    Code:
    Set qDef = Db.QueryDefs("qPT_Generic")
        qDef.Connect = Db.TableDefs("tbYourLinkedTable").Connect
        qDef.SQL = strQuery
    That way your query always connects to the same place you other tables are connected to.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    uoghk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    149
    thank Minty

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to create Dynamic Array
    By Ramtrap in forum Programming
    Replies: 14
    Last Post: 03-21-2018, 10:23 AM
  2. Replies: 4
    Last Post: 08-15-2017, 08:44 AM
  3. Replies: 33
    Last Post: 09-25-2015, 08:39 AM
  4. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  5. Need to create a UI dynamic query builder
    By dollylectric in forum Programming
    Replies: 1
    Last Post: 11-16-2011, 02:09 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums