Calling A Udf (sql Server) From Vb Access "undefined Function In Expression"

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 UD

Solution 1:

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.

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