Can I call a SQL Server 2000 User-Defined Function from Access 2003?

[Steve D]

Does anyone know if this can be done?

[Albert D. Kallal]

Re: Can I call a SQL Server 2000 User-Defined Function from Access 2003?

yes, just create a pass-through query in the query builder and put in

exec sp_yourProc

Then,

currentdb.Execu te "yourqueryN ame"

Can't remember..but you might need:
exec "sp_YourPro c" in the query...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.attcanada.net/~kallal.msn

[Steve DeBettignies]

Re: Can I call a SQL Server 2000 User-Defined Function from Access 2003?

So I assume for a function it would look something like:

intSomeValue = Exec dbo.fncCalcValu e???

Since it's returning a value you can't use .Command or .Execute



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Albert D. Kallal]

Re: Can I call a SQL Server 2000 User-Defined Function from Access 2003?

Ah, ok....was under the impression you just wanted to run a stored proc.
(not return values from a t-sql function)

If you are going to return a value...then you can just use a reocrdset....

You can still use a pass-through query...but you just take the results form
a reocrdset.


dim rstData as dao.recordset


set rstData = currentdb.OpenR ecordSet("YourP assThrouthQuery NameGoesHere")

msgbox "return value = " & rstData(0)

However, you are much better off to use a ADO connection for this. In fact,
you can execute any procedure via the connection object...and not even have
to use a pass through query.

MyADOConnection .YourProcName

So, here is some ado examples...as I don't feel comfortable suggesting to
use dao to grab data from a function on sql server.

http://msdn.microsoft.com/library/de...parameters.asp

Also, since you are not talking about using a storeed procedure..but a t-sql
defined function, then I might be miss-understanding your question.
(you likey have to create a stored procedure that uses that fucntion...and
return values that way).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.attcanada.net/~kallal.msn

[Steve DeBettignies]

Re: Can I call a SQL Server 2000 User-Defined Function from Access 2003?


Thanks for the help. Using ADO with a SP which runs my SQL user-defined
function works. Thanks for the idea and help.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!