I'm trying to call a stored function like this from vba in access:
我试图在访问中从vba调用这样的存储函数:
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?
具体来说,我想知道是否有相同的'adCmdStoredProc'功能?
2 个解决方案
#1
1
"Specifically I'd like to know if there is an equivalent of 'adCmdStoredProc' for functions?"
“具体来说,我想知道是否有相同的'adCmdStoredProc'用于函数?”
But the SQL you're using is a SELECT which references a function:
但是你正在使用的SQL是一个引用函数的SELECT:
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有7个选项。 adCmdUnspecified应该工作;可能也是adCmdUnknown。我会使用adCmdText,但它并不是“相当于”某个函数的adCmdStoredProc。
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
#2
0
Have you tried running it as a SELECT
statement?
您是否尝试将其作为SELECT语句运行?
SELECT *
FROM my_function()
#1
1
"Specifically I'd like to know if there is an equivalent of 'adCmdStoredProc' for functions?"
“具体来说,我想知道是否有相同的'adCmdStoredProc'用于函数?”
But the SQL you're using is a SELECT which references a function:
但是你正在使用的SQL是一个引用函数的SELECT:
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有7个选项。 adCmdUnspecified应该工作;可能也是adCmdUnknown。我会使用adCmdText,但它并不是“相当于”某个函数的adCmdStoredProc。
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
#2
0
Have you tried running it as a SELECT
statement?
您是否尝试将其作为SELECT语句运行?
SELECT *
FROM my_function()