ACCESSIBLESOLUTIONS
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.
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.
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?
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
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
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
Mike
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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