[SOLVED] Use function call in passthrough query?

Use function call in passthrough query?

I have a passthrough query in an Access 2010 application, which I'm using to call a stored procedure on a SQL Server backend. The stored procedure takes a parameter that I need to make dynamic. The problem is this:

Execute spMyProc 'userName' works as expected.

Execute spMyProc getUserName() generates a "syntax error at ')'" message.

Is it possible to use a function as a parameter in a pass-through query?

Also, I should note that I'm migrating a complex Access application to SQL server, and I'm really not well-versed in what I'm doing. Any suggestions on things I'm doing incorrectly will be gratefully received. This particular question is rising from an attempt to change the Record Source of a Form from a simple select statement in the Record Source property to something that can be run on the server.

Solution

You can use this code:

With CurrentDb.QueryDefs("MyPass")
  .SQL = "exec spMyProc '" & getUserName() & "'"
  .Execute
End With

Because getUserName() is a local VBA function, then you need to pre-evaluate the actual string sent to SQL server. As above shows using a saved pass-though is "handy" since you don't have to deal with connection strings etc.