X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Passthrough Queries, Access 2010    
post Jan 10 2017, 03:02 PM

Posts: 169
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.
Go to the top of the page
post Jan 10 2017, 03:13 PM

Access Wiki and Forums Moderator
Posts: 72,155
Joined: 19-June 07
From: SunnySandyEggo


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

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
post Jan 10 2017, 03:39 PM

UA Admin
Posts: 32,580
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).

Go to the top of the page
post Jan 10 2017, 03:50 PM

Posts: 844
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.
Go to the top of the page
post Jan 10 2017, 05:22 PM

UtterAccess VIP
Posts: 2,620
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
   With CurrentDb.QueryDefs("qryPassR")
      .SQL = "SELECT * FROM tblUser_Parameters WHERE Parameters_User = '" & Logged_in_user() & "'"
      Set rst = .OpenRecordset()
   End With
End Sub

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()
   With CurrentDb.QueryDefs("qryPassR")
      .SQL = "sp_UpdateUser '" & Logged_in_user() & "'"
      .ReturnsRecords = False
   End With
End Sub

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
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th May 2018 - 08:25 PM