在直通查询中使用函数调用? [英] Use function call in passthrough query?

问题描述

我在Access 2010应用程序中有一个传递查询，该查询用于在SQL Server后端上调用存储过程.存储过程采用了我需要使其动态的参数.问题是这样的:

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'可以正常工作.

Execute spMyProc getUserName()生成在')'处的语法错误"消息.

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

是否可以在传递查询中使用函数作为参数?

另外，我应该注意，我正在将一个复杂的Access应用程序迁移到SQL Server，并且我对自己的工作并不精通.对于我做错的事情的任何建议将不胜感激.这个特殊的问题来自试图将表单的记录源"从记录源"属性中的简单select语句更改为可以在服务器上运行的东西的问题.

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.

推荐答案

您可以使用以下代码:

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

由于getUserName()是本地VBA函数，因此您需要预先评估发送到SQL Server的实际字符串.如上所示，使用保存的传递是方便的"，因为您不必处理连接字符串等.

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.

这篇关于在直通查询中使用函数调用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！