vba - Calling a UDF (Sql server) from VB Access "Undefined function <function name> in expression" -
i trying call udf (sql server) vb code in access. connection db successful , able run queries on sql server tables. however, when try call udf, throws me error saying undefined function.
please see code below:
private sub cmd_login_click() ' code here set db = currentdb() ssql = "select userid tbl_user_login username = '" & cbo_user & "' , 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) & "') passwordvalid" set recset = db.openrecordset(ssql) ' error undefined function fn_validate_user passwordvalid = recset("passwordvalid")
can see if missing here.
when run standard query in access first processed access database engine, if query refers odbc linked tables. access can recognize access user-defined functions (created vba) not aware of sql server user-defined functions.
in order use sql server user-defined function need use pass-through query. name suggests, bypasses access database engine , sends query directly remote database (via odbc). vba code this:
dim db dao.database, qdf dao.querydef, recset dao.recordset dim ssql string, passwordvalid boolean set db = currentdb ssql = "select fn_validate_user(" & gb_userid & ",'" & hash(me.txt_password + cbo_user) & "') passwordvalid" set qdf = db.createquerydef("") ' .connect property 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
Comments
Post a Comment