CODEWITHSUNDEEP
vbams-accessstored-proceduressql-server-2008-r2ms-access-2010
Kunal
Kunal

Reputation: 63

Calling a SQL server procedure from a function in VBA

I am trying to call a SQL Server procedure to validate user from a function in VBA.

Please see the code below:

proc_validate_user accepts userId and password as input parameters and returns a bit value of 0 or 1

function to call this proc is as under:

Public Function ExecUDF(userId As Integer, pwd As String)

    Dim cmd As DAO.Command
    Dim par As DAO.Parameter
    Dim ret As Variant

    Set cmd = New DAO.Command
    cmd.ActiveConnection = CurrentDb.Connection
    cmd.CommandText = "proc_validate_user"
    cmd.CommandType = adCmdStoredProc

    'Define the input and output variables and append them to the collection
    Set par = cmd.CreateParameter("@userID")
    cmd.Parameters.Append par
    Set par = cmd.CreateParameter("@pwd")
    cmd.Parameters.Append par
    Set par = cmd.CreateParameter("@isValid", adParamOutput)
    cmd.Parameters.Append par

    cmd.Parameters("@userID") = userId
    cmd.Parameters("@pwd") = pwd
    cmd.Execute

'and then assign the stored procedure return value to the access variable

    ret = cmd.Parameters("@isValid").Value



End Function

I am getting an compilation error saying User defined type not defined at "Dim cmd As DAO.Command"

Upvotes: 0

Views: 2301

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

You are mixing up the DAO and ADO object models. DAO is a completely different model, not a "subset of ADO". There is no DAO.Command object.

SQL Server stored procedures that produce output parameters can be a nuisance to work with using DAO. The ADO model is much better aligned with SQL Server, so I would suggest using code like this:

Public Function ExecUDF(userId As Integer, pwd As String) As Boolean
    Dim con As ADODB.Connection, cmd As ADODB.Command, connStr As String, isValid As Boolean
    ' remove "ODBC;" prefix from the .Connect property of an existing ODBC linked table
    connStr = Mid(CurrentDb.TableDefs("dbo_my_table").Connect, 6)
    Set con = New ADODB.Connection
    con.Open connStr
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "proc_validate_user"
    cmd.Parameters.Append cmd.CreateParameter("@userID", adInteger, adParamInput, , userId)
    cmd.Parameters.Append cmd.CreateParameter("@pwd", adVarWChar, adParamInput, 50, pwd)
    cmd.Parameters.Append cmd.CreateParameter("@isValid", adBoolean, adParamOutput)
    cmd.Execute
    isValid = cmd.Parameters("@isValid").Value
    Set cmd = Nothing
    con.Close
    Set con = Nothing
    ExecUDF = isValid  ' return the value
End Function

Upvotes: 1

Related Questions