CODEWITHSUNDEEP
ms-accessvbaado
David
David

Reputation: 2251

Calling MySql stored function rather than a stored procedure with ADO Command object

I'm trying to call a stored function like this from vba in access: 

SELECT my_function();

If it were a stored procedure it would be like this: 

CALL my_procedure();

For the stored procedure I can use: 

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")

With cmd
    Set .ActiveConnection = oConn 'ADODB connection created elsewhere
    .CommandType = adCmdStoredProc
    .CommandText = "my_procedure"
End With

cmd.execute

Specifically I'd like to know if there is an equivalent of 'adCmdStoredProc' for functions?

Upvotes: 2

Views: 2907

Answers (2)

HansUp
HansUp

Reputation: 97131

"Specifically I'd like to know if there is an equivalent of 'adCmdStoredProc' for functions?"

But the SQL you're using is a SELECT which references a function:

SELECT my_function();

You have 7 choices from the CommandTypeEnum. adCmdUnspecified should work; probably adCmdUnknown too. I would use adCmdText, but it's not really "the equivalent of" adCmdStoredProc for a function.

CommandTypeEnum Constants
Constant         Value  Description
adCmdFile        256    Evaluate as a previously persisted file
adCmdStoredProc    4    Evaluate as a stored procedure
adCmdTable         2    Have the provider generate a SQL query and return all rows from the specified table
adCmdTableDirect 512    Return all rows from the specified table
adCmdText          1    Evaluate as a textual definition
adCmdUnknown       8    The type of the CommandText parameter is unknown
adCmdUnspecified  -1    Default, does not specify how to evaluate

Upvotes: 1

Taryn
Taryn

Reputation: 247840

Have you tried running it as a SELECT statement?

SELECT *
FROM my_function()

Upvotes: 0

Related Questions