Scalar-Valued UDF Function

[Guest]

I've created new UDFs that return primary kesy from a table based on a string
passed. I'm using these within my stored procedures with no problem, so I
know my UDF's are working properly.

What I want to do now is use these same UDF's within my Access Application
to Return a looked up Primary Key or to find the Maximum Primary Key. How can
I retrieve a UDF Value in Access. (i.e. assign the returned value to a
variable.) Right now, I'm running Views that return a Maximum Value (in some
cases) and I do so by creating a recordset and then looking up the value). I
know this can't be the most efficient manner in doing this. I woudl assume
there is a way I can execute a UDF and place the returned value into a
variable, wihtout having to create a recordset?

Thanks

 

[Sylvain Lafontaine]

I don't know if you can call a UDF that return something else than a
resultset (or recordset) directly from ADP - as I never tried it - but I
suppose you could always write a SP to wrap the UDF and return the answer
with a Return statement at the end of the SP.

 

[Peter Yang [MSFT]]

Hello Greg,

I understand that you'd like to use UDF in your ADP project to return a
maximum value of primiary key of a table. If I'm off-base, please let me
know.

Based on my test, you could try the following function to do this job by
using ADO

Function MyExec(ByVal strsql As String) As Variant

Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
conn.Open CurrentProject.Connection.ConnectionString

rs.Open strsql, conn

If rs.EOF = False Then
MyExec = rs(0)
End If

rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing
End Function

Function test()
Debug.Print MyExec("Select GETDATE()")
Debug.Print MyExec("select dbo.testf(1)")
End Function

If anything is unclear or you have any questions on this, please feel free
to let's know. I look forward to your reply. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.