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.
Please see the code below:
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.
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
