By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

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

P: n/a
Does anyone know if this can be done?
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
yes, just create a pass-through query in the query builder and put in

exec sp_yourProc

Then,

currentdb.Execute "yourqueryName"

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

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #2

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

intSomeValue = Exec dbo.fncCalcValue???

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!
Nov 13 '05 #3

P: n/a
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.OpenRecordSet("YourPassThrouthQueryNameG oesHere")

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
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 13 '05 #4

P: n/a

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!
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.