• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Create a Passthrough query dynamically

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.
  • 3
  • 2
1 Solution
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
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?
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
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.

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?
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now