Calling a SQL server procedure from a function in VBA

Question

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"

What is DAO? || you have a typo and aren't closing your function. — TZHX
– TZHX, Commented Apr 2, 2015 at 9:52
DAO stands data access object and is a subset of ADO. It is used very commonly and I have used it in other part of my code where it works beautifully. For ex: DAO.Recordset — Kunal
– Kunal, Commented Apr 2, 2015 at 9:59
Yes... But is it included / in scope? The error message seems to suggest that the compiler doesn't know what DAO.Command is. — TZHX
– TZHX, Commented Apr 2, 2015 at 10:03
DAO.Recordset works fine and when I was checking help in how to call a sql server procedure from VB, I came to know about these commands ADODB.command and CreateParameter functions etc. I tried to write the code with ADODB as well as DAO and both give me the same error. — Kunal
– Kunal, Commented Apr 2, 2015 at 10:06
both give the same error of the type DAO.Command not being defined? — TZHX
– TZHX, Commented Apr 2, 2015 at 10:07

Gord Thompson · Accepted Answer · 2015-04-02 13:04:25Z

1

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

edited Apr 2, 2015 at 13:04

answered Apr 2, 2015 at 11:19

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kunal Over a year ago

Thank you Gord, I figured this out already as I mentioned. I am however stuck with using the function that i just created in my cmd_Login_Click() routine. I am assigning it to value : PasswordValid = ExecUDF(userId, pwd) and it throws me an error saying "3251 operation is not supported for this type of object". I truly appreciate your help.

Gord Thompson Over a year ago

If it is just a matter of getting the value back from the VBA function then have a look at my updated code.

Kunal Over a year ago

Hi Gord, I get "procedure or function has too many arguments" after I modified the code based on your suggestion. my DB proc looks like this

CREATE PROCEDURE dbo.proc_validate_user (@userID int, @hashPassword varchar(MAX), @isValid bit output) AS DECLARE @ret bit   if EXISTS(select * from TBL_User_Login where userID = @userID and password = @hashPassword)     SET @ret = 1   else     SET @ret = 0 SELECT @isValid =@ret GO

Kunal Over a year ago

By the way, I already changed the code cmd.Parameters.Append cmd.CreateParameter("@pwd", adVarWChar, adParamInput, 50, pwd) to cmd.Parameters.Append cmd.CreateParameter("@hashPassword", adVarWChar, adParamInput, 50, pwd)

Kunal Over a year ago

Got it resolved by changing the proc defintion in SQL Server. thank you Gord

Collectives™ on Stack Overflow

Calling a SQL server procedure from a function in VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related