从VB Access调用UDF(SQL服务器)"Undefined function< function name>"在表达中" [英] Calling a UDF (Sql server) from VB Access "Undefined function <function name> in expression"

问题描述

我正在尝试通过Vb代码在访问中调用udf(SQL服务器).与数据库的连接成功，我可以在SQL Server表上运行查询.但是，当我尝试调用UDF时，会抛出一个错误，提示未定义的函数.

I am trying to call a udf (SQL server) from Vb code in access. Connection to DB was successful and I am able to run queries on SQL server tables. However, when I try to call the UDF, it throws me an error saying undefined function.

请参见下面的代码:

Private Sub cmd_Login_Click()
    ' some code here
    Set db = CurrentDb()

    sSQL = "SELECT UserID FROM TBL_User_Login WHERE UserName = '" & cbo_User & "' AND Status = 0"
    Set recset = db.OpenRecordset(sSQL)


    recset.Close
    Set rectset = Nothing

    sSQL = "SELECT fn_validate_user(" & gb_UserId & ",'" & Hash(Me.txt_Password + cbo_User) & "') AS PasswordValid"
    Set recset = db.OpenRecordset(sSQL)  ' this is where i get error for undefined function fn_validate_user
    PasswordValid = recset("PasswordValid")

有人可以看看我是否在这里丢失了东西.

Can someone see if I am missing something here.

推荐答案

在Access中运行标准查询时，Access数据库引擎首先对其进行处理，即使该查询引用的是ODBC链接表. Access可以识别 Access 用户定义的功能(使用VBA创建)，但是无法识别SQL Server用户定义的功能.

When you run a standard query in Access it is first processed by the Access Database Engine, even if that query refers to ODBC linked tables. Access can recognize Access user-defined functions (created with VBA) but it is not aware of SQL Server user-defined functions.

为了使用SQL Server用户定义的函数，您需要使用传递查询.顾名思义，它绕过了Access数据库引擎，并将查询直接发送到远程数据库(通过ODBC).这样做的VBA代码如下所示:

In order to use a SQL Server user-defined function you need to use a pass-through query. As the name suggests, it bypasses the Access Database Engine and sends the query directly to the remote database (via ODBC). The VBA code to do that would look something like this:

Dim db As DAO.Database, qdf As DAO.QueryDef, recset As DAO.Recordset
Dim sSQL As String, PasswordValid As Boolean
Set db = CurrentDb
sSQL = "SELECT fn_validate_user(" & gb_UserId & ",'" & Hash(Me.txt_Password + cbo_User) & "') AS PasswordValid"
Set qdf = db.CreateQueryDef("")
' get .Connect property from existing ODBC linked table
qdf.Connect = db.TableDefs("TBL_User_Login").Connect
qdf.ReturnsRecords = True
qdf.SQL = sSQL
Set recset = qdf.OpenRecordset(dbOpenSnapshot)
PasswordValid = recset.Fields("PasswordValid").Value
recset.Close
Set recset = Nothing
Set qdf = Nothing

这篇关于从VB Access调用UDF(SQL服务器)"Undefined function< function name>"在表达中"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！