Can MS-Access VBA execute an MS_SQL User Defined Function?

[sTeve]

Is it possible to execute a UDF from a VBA module within Access? If
so, is there some sample code I can learn from?

thanx,

Steve Shapiro
University of Oregon

 

[Michel Walsh]

Hi,



Sure. Just be sure the function is public, inside a standard module, and use it as if it was
part of the SQL language:


SELECT myFunction( field1, field2, ... ) FROM ...


unless you meant to run a MS SQL Server UDF in a VBA procedure? then, again, use an SQL
statement that SELECT it as result.




Hoping it may help,
Vanderghast, Access MVP

 

[sTeve]

Thanks Michael,

Please let me clarify what I'm trying to do, and maybe you can help me
with the code?

On the SQL Server, I have the following UDF:

CREATE FUNCTION rc4_fnEncryption
(@p_cKey varchar(255),@p_cData varchar(255)) RETURNS varchar(255)

It accepts a key and a string, runs it through an encryption routine,
and returns it.

I would like to run this UDF from a VBA module within Access (where
the key will be stored. We are using ADO, so that means no pass-thru
queries (right?)

------------------------------------------------------------------------------------
I've created a Stored Procedure to call the function (and it works):

CREATE PROCEDURE dbo.sp_EnDeCrypt(@chrWD char(12), @chrSTRING
varchar(255), @cResult varchar(255) OUTPUT) AS
SET @cResult = dbo.rc4_fnEncryption(@chrWD,@chrString)
GO

---------------------------------------------------------------------------------
I can successfully call the Stored Procedure from within the Access
Module with code:

Const connectionString As String = "Driver=SQL Server;
Server=MYServer;Database=MYDatabase;Trusted_Connection=Yes"

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cnn = New ADODB.Connection
cnn.Open connectionString
Set cmd = New ADODB.Command

cmd.ActiveConnection = cnn
cmd.CommandText = "sp_EnDeCrypt"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("chrWD", adWChar, adParamInput, 12,
"cnst_UO_ORSA")
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("chrString", adWChar, adParamInput, 255,
strString)
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("cResult", adWChar, adParamOutput, 255)
cmd.Parameters.Append prm

cmd.Execute

MsgBox cmd.Parameters("cResult")
Set prm = Nothing
Set cmd = Nothing
Set cnn = Nothing
End Function

-----------------------------------------------------------------------------------------------------------------

Do you know how to write the code that will execute the UDF and return
a value?

thank you,

Steve Shapiro
University of Oregon

 

[Michel Walsh]

Hi,


In Pubs:


=========
USE Pubs
GO

CREATE FUNCTION FullName( @f As varchar(255), @l As varchar(255)) RETURNS varchar(255)
BEGIN
RETURN ( @f + ' ' + @l )
END

GO
SELECT dbo.FullName(au_lname, au_fname) as fn FROM authors
=======


or, if you prefer, after I created FullName, from any tools, in Access, I just type, in the
immediate debug window:

---------------------
? CurrentProject.Connection.Execute("SELECT dbo.fullname('Joe', 'Smith') ").Fields(0).Value

Joe Smith
----------------------


Hoping it may help,
Vanderghast, Access MVP