Passthrough Queries, Access 2010
Jan 10 2017, 03:02 PM
Joined: 3-April 07
From: Arecibo, PR
Can a SQL passthrough query in Access use a VBA function to pass value as a parameter back to SQL?
SELECT tblUser_Parameters.* FROM tblUser_Parameters WHERE (((tblUser_Parameters.Parameters_User)=Logged_in_user()));
I want the above SQL passthrough to use my VBA Logged_in_user() function, this function returns the current logged in Windows user. The above passthrough fails because it tries to use the Logged_in_user() in SQL, and of course since the function doesn't exist it fails.
Jan 10 2017, 03:13 PM
Access Wiki and Forums Moderator
Joined: 19-June 07
I believe you already answered your question. A passthru query passes all processing of the query to the server. And since you're passing a VBA function, which SQL Server does not have, then you're getting an error. You could try converting your VBA function into a SQL Server stored procedure to make the passthru query work.
Just a thought...
Jan 10 2017, 03:39 PM
Joined: 20-June 02
From: Newcastle, WA
In order to make this work in a Passthru, you'll need to edit the SQL in the query, using a VBA procedure to insert the specific loggedInUser's ID (numeric or string, whichever your VBA returns).
Jan 10 2017, 03:50 PM
Joined: 26-January 14
From: London, UK
In SQL Server you can use the SYSTEM_USER function to return the name of the user's Windows login:
Note that in SQL Server, login name can be different to user name. I guess that login name is what you want.
Jan 10 2017, 05:22 PM
Joined: 12-April 07
From: Edmonton, Alberta Canada
The answer is no, but you as others stated can include the RESULT of the VBA functions.
So, to make your code work in VBA, you should use this:
Public Sub Test5()
Dim rst As DAO.Recordset
.SQL = "SELECT * FROM tblUser_Parameters WHERE Parameters_User = '" & Logged_in_user() & "'"
Set rst = .OpenRecordset()
Note in above I use a “handy” “dandy” PT query for use everywhere in my code.
So say you want to execute a store procedure, but pass your user function, you could use this:
Public Sub Test6()
.SQL = "sp_UpdateUser '" & Logged_in_user() & "'"
.ReturnsRecords = False
Note in the second example because I am using .Execute, then if the store procedure does not return records, but only runs T-SQL code, then you can see that simply changing returnsRecordsd to true/false as required allows one to run Store process, or T-SQL that may or may not return values.
Keep in mind that if the VBA function is to operate on each row value in the table, then you can’t use a pass-through query, but you CAN create a T-SQL functions that are global and work just like VBA functions. So just like VBA allows one to create a function that returns a single value that you can use in forms, code, and even forms and even in SQL expressions?
Well SQL server ALSO allows one to create such functions (they are called scaler functions). Once you re-create that function in T-SQL code, then in T-SQL store procedure code, or even in SQL expressions, you are free to use and include that function. This is a great tip for some migration projects since we had a global GST() function in VBA that was used for a lot of tax calculations. When we moved the quires to SQL server, we simple re-wrote the GST() function in T-SQL and then we were able to cut + paste most of our SQL from Access to T-SQL SQL that used the cool tax function.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
|Search Top Lo-Fi||23rd May 2017 - 01:39 PM|