calling sql server udf in adp from vba

[Margaret D via AccessMonster.com]

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?

 

[aaron.kempf]

select dbo.myfunction(mydate) from mytable

you just wrote a function called myfunction.. so you pass it an arg in
a select statement

 

[Margaret D via AccessMonster.com]

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?

 

[giorgio rancati]

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

 

[Margaret D via AccessMonster.com]

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.

 

[giorgio rancati]

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

 

[giorgio rancati]

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") & "')")
....
....

 

[aaron.kempf]

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.

 

[Vadim Rapp]

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

 

[giorgio rancati]

=dlookup("dbo.myfunction(" & <your parameter> & ")",
"sysobjects","name='sysobjects'")

It works

only one small optimization

 

[aaron.kempf]

wow that's sharp i mean jeez
i didnt know you could pass 'top 1' into dlookup

 

[Vadim Rapp]

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

 

[giorgio rancati]

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