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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.