 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / January 2006calling sql server udf in adp from vba
I am using Sql Server 2000 backend with an Access 2002 ADP frontend. My Sql Server UDFs appear in the Queries container in Access, but I can't find how to call them. I am trying to call a scalar function to which I pass a date. I want to assign the result to a control on the form. Can anyone help? select dbo.myfunction(mydate) from mytable you just wrote a function called myfunction.. so you pass it an arg in a select statement >select dbo.myfunction(mydate) from mytable > >you just wrote a function called myfunction.. so you pass it an arg in >a select statement I'm not selecting the argument or anything from a table, so what do you suggest I use as a target table? I want to call this function from the ADP (not transact sql), either via a control or VBA. I tried putting this in the control source property of a textbox, but got #NAME: select dbo.intAcadYrIDFromDateRange(date()) I also tried: =intAcadYrIDFromDateRange(date()) Any other suggestions? Hi Margaret , You can create a sub or a vba function that call a t-sql function i.e. ---- Function MyIsoWeek(ByVal Dt As Date) As Integer Dim rs As New ADODB.Recordset Dim strSql As String strSql = "Select dbo.ISOweek('" & Format(Dt, "yyyymmdd") & "')" rs.Open strSql, CurrentProject.Connection If rs.EOF = False Then MyIsoWeek = rs(0) End If rs.Close Set rs = Nothing End Function ---- bye  SignatureGiorgio Rancati [Office Access MVP] > I am using Sql Server 2000 backend with an Access 2002 ADP frontend. My Sql > Server UDFs appear in the Queries container in Access, but I can't find how > to call them. I am trying to call a scalar function to which I pass a date. > I want to assign the result to a control on the form. > > Can anyone help? Thank you. So the answer is that even though the function is returning a scalar value and appears to be in an ADP container, you have to create an ADO recordset to store the value in order to return it via ADO? I was hoping it would be more seamless as if it was actually an ADP function. Thanks for your help. >Hi Margaret , > [quoted text clipped - 27 lines] >> >> Can anyone help? > Thank you. > > So the answer is that even though the function is returning a scalar value > and appears to be in an ADP container, you have to create an ADO recordset to > store the value in order to return it via ADO? yes > I was hoping it would be more seamless as if it was actually an ADP function. I am sorry, I don't know an easier way. You can also create a generic VBA function ---- Function MyExec(ByVal strsql As String) As Variant Dim rs As New ADODB.Recordset rs.Open strsql, CurrentProject.Connection If rs.EOF = False Then MyExec = rs(0) End If rs.Close Set rs = Nothing End Function ---- then execute it ---- Debug.print MyExec ("Select @@Servername") Debug.print MyExec ("Select GETDATE()") Debug.print MyExec ("Select dbo.ISOweek('" & Format(Dt, "yyyymmdd") & "')") ... ... ---- bye SignatureGiorgio Rancati [Office Access MVP] > Hi Margaret , I am sorry > You can create a sub or a vba function that call a t-sql function You can create a sub or a vba function that calls the t-sql function SignatureGiorgio Rancati [Office Access MVP] update mytable set myfield = dbo.myfunction(mydate) where pk= 123456 i think that is better, easier-- than going through the vb side of the equation. Hello Margaret, You wrote in conference microsoft.public.access.adp.sqlserver on Wed, 25 Jan 2006 19:03:11 GMT: MDA> I am using Sql Server 2000 backend with an Access 2002 ADP frontend. MDA> My Sql Server UDFs appear in the Queries container in Access, but I MDA> can't find how to call them. I am trying to call a scalar function to MDA> which I pass a date. I want to assign the result to a control on the MDA> form. For the controlsource, you can specify either =expression , or the name of a field returned in form's recordsource. If it's expression, it must be biult-in or your custom function. The following will work: =dlookup("dbo.myfunction(" & <your parameter> & ")", "sysobjects","name='sysobjects'") Or, you can include dbo.myfunction in the recordsource of the form, and specify that field as controlsource for the control. Vadim Rapp > =dlookup("dbo.myfunction(" & <your parameter> & ")", > "sysobjects","name='sysobjects'") It works :-) only one small optimization ---- =dlookup("TOP 1 dbo.myfunction(" & <your parameter> & ")", "sysobjects") ---- bye SignatureGiorgio Rancati [Office Access MVP] wow that's sharp i mean jeez i didnt know you could pass 'top 1' into dlookup gr> only one small optimization gr> ---- gr> =dlookup("TOP 1 dbo.myfunction(" & <your parameter> & ")", gr> "sysobjects") As you know, sysobjects has many, many rows; without the criteria, it's not clear how it will work. There's possibility that sql server would collect all rows, order them somehow, then take 1. (I'm sure it wouldn't in fact, but it _could_, i.e. nothing says it's impossible). With the criteria, theer's assurance that it will be 1 returned row exactly. And programming with assurance rather than with assumptions is good. Vadim > As you know, sysobjects has many, many rows; without the criteria, it's not > clear how it will work. There's possibility that sql server would collect > all rows, order them somehow, then take 1. no, it return the first row ordered by clustered index Bye SignatureGiorgio Rancati [Office Access MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.