Create a Passthrough query dynamically

Posted on 2004-11-24
Last Modified: 2008-02-01
We have a few Passthrough queries stored on the client machine to speed filling a names combo box but because of security requirements have concluded these queries need to be unique to whomever logged into the program.

Said another way, we need multiple Passthrough queries with some unique identifier i.e. qryPTclient_dd, qyrPTclient_sp - one for each user of the system. It's not practical to create these queries in advance and would rather use a querydef technique. Is anyone familiar with if/how this can be done since we've been told using a querydef is not possible?

Thanks, in advance to anyone that replys.
    LVL 8

    Expert Comment

    I do this by creating a module called pt, then for each of the pass through queries do this:

    Function pt_SelectCustomer (CustomerID as long) as integer
    on error goto errhandler

    dim q as dao.qdef
    dim s as string

    set q = currentdb.querydefs ("pt_SelectCustomer")

    q.sql = "pr_selectcustomer " & CustomerID

    set q = nothing
    pt_SelectCustomer = 0
    exit function

         pt_SelectCustomer  = -999

    end function

    Author Comment

    SimonLarsen: I'm a little confused by your code. It appears to be a querydef with a customerid concatenated to it - which if it is, isn't my question or problem. The question is how do you create a passthru query (includes ODBC; DSN, etc) using a querydef or some similar technique?
    LVL 8

    Expert Comment

    It shows how to rewrite a passthrough with a parameter using qdef, perhaps I should have asked why you were told this was not possible.

    I was under the impression you wanted to rewrite the text of the passthrough queries adding an identifier for the logged in person? Or are you trying to create new ones?

    The basic principle holds, Create one pass through and just change the sql string as required. For changing the connection string use something like this:

    Function FixptCon(svr As String, db As String)
    Dim qdf As DAO.QueryDef

    If svr = "" Then
    End If

    If db = "" Then
        db = "YOURDEFAULTDB"
    End If

    For Each qdf In CurrentDb.QueryDefs
        If Left(qdf.Connect, 4) = "ODBC" Then
            qdf.Connect = "ODBC" & _
                ";DRIVER=SQL Server" & _
                ";SERVER=" & svr & _
                ";DATABASE=" & db & _
                ";LANGUAGE=us_english" & _
                ";Regional=Yes" & _
        End If

    End Function
    LVL 18

    Expert Comment

    be careful when using pass through queries and changing the SQL property.  I ran into problems in a multi user environment before, where everyone is using the same front end.


    Author Comment

    SimonLarsen: Need a little more guidance if you don't mind. You say to create one passthru - Is this a stored query or a dynamic query created with a query def? If it is using a querydef, how do you do it such that it ends up as a Passthru? I tried it, and Access just creates a normal Select query. If it isn't, how do you pass a sql string to a query that is basically a sql string already?

    Further, could you provide an example of svr = "YOURDEFAULTSERVER" i.e. Domain name? Computer name? Path?
    And db="YOURDEFAULTDB" i.e. Frontend database? Backend database? Path?
    LVL 8

    Accepted Solution

    My normal way of doing it is:

    Create and save a stored pass through query.

    Create a function specifically for rewriting the sql property of the query object.

    Whenever you need to pass a parameter just call the function.

    Now you could in theory built one single passthrough and use that as and when required.

    I find this to be too complex to maintain. So I build all the ones I'll need and simply pass parameters to them. Makes the code easier to follow. I am also loath to build new objects in code.

    For moving the front end to a different environment (say dev to prod) I usually bung a function into my utilities that does a rewrite of the connection properties for all the stored passthrough queries. This is the function above. For YOURDEFAULTSERVER in my dev environment the sql server is called dev001 so I put "dev001". This is usually the windows computer name of the server. db is the name of the SQL backend database. e.g. "Accounts"

    A quick little test function I usually have floating around (normal right beneath the DAO conn rewrite) is:

    Function ocon() As ADODB.Connection

    svr = "Dev001"
    db = "Accounts"

    Dim con As ADODB.Connection

    Set con = New ADODB.Connection

    con.Open "Provider=sqloledb;" & _
               "Data Source=" & svr & ";" & _
               "Initial Catalog="  & db & ";" & _
               "Integrated Security=SSPI"

    Set ocon = con

    End Function

    If you copy that code, change the db and svr values and step through it you'll know if you have the values right.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now