How to call a SQL server user defined function UDF from MS Access code

[accesser2003]

I want to call a SQL server User Defined Function UDF from MS Access code and get its returned value.

I successded in calling it but I dont know how to get its returned value. The code I used is as follow:

MyIx = 50034
MySt = "20/12/2007 20:30:00"

Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administrator;DATABASE=Bahrain;Trusted_Connection=Yes")

SQL = "FindPSF" & " " & MyIx & "," & "'" & Format(MySt, "dd-mmm-yyyy HH:MM:SS") & "'"
db.Execute SQL, dbSQLPassThrough

The function FindPSF I want to call is as follow:

CREATE FUNCTION dbo.FindPSF (@Ix int,@St datetime)
RETURNS datetime
AS
BEGIN
Declare @PSF datetime
SELECT @PSF= Max(Finish)
FROM dbo.SelectedEmployeesWorkMode
WHERE (NOT (Finish IS NULL)) AND (DATEDIFF(s, Finish, @St) >= 0) AND (Indx = @Ix)

RETURN @PSF

END

 

[datAdrenaline]

Below are code samples (one DAO and on ADO) of how you would get the result of a function ... the sample function I used (fn_Timevalue) excepts one argument and returns a value .... so you can easily adapt them for your needs.

The premise is that you form a SQL statement with the function call with all the arguments resolved into literals, then execute that statement on the server. The server returns the value and the SQL statement returns the value as a field in a single row recordset ...

Private Function runFunDAO() As Variant
 
    Dim strSQL As String
 
    strSQL = "SELECT [COLOR=blue]dbo.fn_TimeValue('1/1/2008 6:30 am')[/COLOR] As MyTime"
 
    With CurrentDb.CreateQueryDef("~" & Int(Timer), strSQL)
        .Connect = "<valid ODBC connect string>"
        runFunDAO = .OpenRecordset.Fields(0)
    End With
 
End Function
 
 
Private Function runFunADO() As Variant
 
    Dim strSQL As String
 
    strSQL = "SELECT [COLOR=royalblue]dbo.fn_TimeValue('1/1/2008 6:30 am')[/COLOR] As MyTime"
    With CreateObject("ADODB.Connection")
       .Open "<valid ADO connection string>"
       runFunADO = .Execute(strSQL).Fields(0)
       .Close
    End With
 
End Function

 

[accesser2003]

I am highly appreciative for you datAdrenaline.
I have tried the first option and make it as follow:

strSQL = "SELECT dbo.FindPSF" & "(" & MyIx & "," & "'" & Format(MySt, "dd-mmm-yyyy HH:MM:SS") & "') As MyFn"

With CurrentDb.CreateQueryDef("~" & Int(timer), strSQL).Connect = "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administrator;DATABASE=Bahrain;Trusted_Connection=Yes"
PSF = .OpenRecordset.Fields(0)
End With

However, it highlight the "With" and gives me the following error:
"With object must be user-defined type, object or variant.

What is the reason?

 

[datAdrenaline]

The .Connect should be the NEXT code line under the WITH code line, just like above ...

You see ... you are working WITH the created Query Def ... not the Connect property of the created query ...

[SIZE=1][COLOR=#008000]strSQL = "SELECT dbo.FindPSF" & "(" & MyIx & "," & "'" & Format(MySt, "dd-mmm-yyyy HH:MM:SS") & "') As MyFn"[/COLOR][/SIZE]
 
[SIZE=1][COLOR=#008000]With CurrentDb.CreateQueryDef("~" & Int(timer), strSQL)[/COLOR][/SIZE]
[SIZE=1][COLOR=#008000]   .Connect = "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administrator;DATABASE=Bahrain;Trusted_Connection=Yes"[/COLOR][/SIZE]
[SIZE=1][COLOR=#008000]   PSF = .OpenRecordset.Fields(0)[/COLOR][/SIZE]
[SIZE=1][COLOR=#008000]End With[/COLOR][/SIZE]

Does that make sense?

 

[accesser2003]

It works nice and perfect.

I am highly appreciative for you. Can I to send you email on your personal one?

Thanks & Regards

 

[datAdrenaline]

You're very welcome!!! ... Good luck on your project!

 

[datAdrenaline]

Hello Accesser2003 ...

I have a slight mod to inform you about ... in the code I provided, you will end up with a query the has a name that starts with "~" in your database window ... That was not my intent ... so to make the query a TEMP object to where it will not become an object in the database window, make the following modifications (don't set the name in the CreateQueryDef() method ...

[SIZE=1][COLOR=#008000]strSQL = "SELECT dbo.FindPSF" & "(" & MyIx & "," & "'" & Format(MySt, "dd-mmm-yyyy HH:MM:SS") & "') As MyFn"[/COLOR][/SIZE]
 
[SIZE=1][COLOR=#008000]With CurrentDb.CreateQueryDef("", strSQL)[/COLOR][/SIZE]
[SIZE=1][COLOR=#008000]  .Connect = "ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administrator;DATABASE=Bahrain;Trusted_Connection=Yes"[/COLOR][/SIZE]
[SIZE=1][COLOR=#008000]  PSF = .OpenRecordset.Fields(0)[/COLOR][/SIZE]
[SIZE=1][COLOR=#008000]End With[/COLOR][/SIZE]