asked on

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.

SimonLarsen

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

errhandler
pt_SelectCustomer = -999

end function

ACCESSIBLESOLUTIONS

ASKER

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?

SimonLarsen

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
svr = "YOURDEFAULTSERVER"
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" & _
";Trusted_Connection=Yes"
End If
Next

End Function

Data-Man

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.

Mike

ACCESSIBLESOLUTIONS

ASKER

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?

ASKER CERTIFIED SOLUTION

SimonLarsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial