Use function call in passthrough query?

Question

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.

What is getUserName() - SQL Server or Access function? Access I'm assuming...? — VBlades, Feb 25 '15 at 22:56

score 4 · Accepted Answer · edited May 23 '19 at 13:24

4

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.

edited May 23 '19 at 13:24

Bullfrog

207
4
16

answered Feb 26 '15 at 06:09

Albert D. Kallal

42,205
3
34
51

That's an excellent idea, especially since I'm going to face this situation multiple times. thanks! – nwhaught Feb 26 '15 at 16:18

Use function call in passthrough query?

1 Answers1

Linked