How to call a sql custom function from an Access project?

see:

https://www.experts-exchange.com/videos/183/Executing-a-SQL-Server-Function-from-Within-Access.html

This is video in EE library.

Also change the function a bit:

CREATE FUNCTION [dbo].[fnOrientLastNoteID]
(
	@Orientationid	AS	int
)
RETURNS int
AS
BEGIN
	DECLARE @Result AS int 

	select @Result =   MAX(ID) 
FROM         dbo.OrientationNotes
where OrientationID = @OrientationID;
--GROUP BY OrientationID  -- you don't need to group


	RETURN @Result;

 

END

GO

Select allOpen in new window

@eghtebas,

First thanks for replying,

I am having a hard time opening that video from my pc, since the first part is working (and I see you approve that, since the only enhancement you had was removing the unnecessary group by, must be the rest you're okay with..) is it possible for you to post the code  I need in the Access end in order to make that work?

P.S. I see that link you posted partially refers to pass-through queries, which not applicable to ADP's.

FYI- If you're wondering why I need that function, and not use Access build-in Dmax(), take a look at the following.
https://www.experts-exchange.com/questions/28655341/Access-2003-issue.html

Thanks,
Ben

Hi,

Perhaps the easiest way to do it in ADP, is to use a store procedure instead of sql function?

See following link,
http://bytes.com/topic/access/answers/748448-calling-scalar-value-sql-server-function-adp

What does it take to convert that function to a SP?

Thanks,
Ben

I was busy with some projects. I will build a sample in access and upload it here. I will stay with function solution if it is okay. We can have a stored procedure solution in another question.

Mike


Make any adjustments you need to my test table below:

create table OrientationNotes(
ID int
, OrientationID int
)

insert OrientationNotes(ID, OrientationID) values
(1, 12),
(2, 12),
(3, 11),
(5, 11)

select * from OrientationNotes
ID, OrientationID
1	12
2	12
3	11
5	11

I will supply 12, to get Max(ID) = 2 to test this.

Select allOpen in new window

Hi eghtebas,

FYI- I had already someone help me convert this into a SP, however that didn't alter the results.(#name?)
Waiting to see what you can come up with..

Please bear in mind that the purpose here is to try something that will work more efficient than the DMAX build-in function in Access.

Thanks,
Ben

@eghtebas,
Any hope for us?
Would appreciate if this can be done today, as I am not coming in to work tom.
Thank you.

I use the following code to execute a function from Access

Public Function AccessFunctionName(ParameterToPasstoFunction) As Long

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = sConnect - this is the connection details set elsewhere for connecting to the database
cnn.Open

AccessFunctionName= cnn.Execute("SELECT dbo.SQLFunctionName(" & ParameterToPasstoFunction& ")")(0)

cnn.Close
Set cnn = Nothing

End Function

The parameter in this case is an Integer - take care to encase in single quotes if a string.


Kelvin

@Kelvin,

1- I am getting a syntax error message when compiling the following line
AccessFunctionName= cnn.Execute("SELECT dbo.SQLFunctionName(" & ParameterToPasstoFunction& ")")(0)

2- In an ADP, how do I find out what is the connection string to the DB?

Ummm, I took that code out of an accdb (Access 2013). Access 2013 won't open my old adp's but I think that line should be

Set cnn = CurrentProject.AccessConnection

Hopefully fixing this may well fix the compile error as you'll have a valid connection


Kelvin

See attached
Untitled.png

That worked, thank you!

@Kelvin,
If you think its possible to further optimize the performance of this method (Like calling the function directly without using access module), I will post another question and you'll take it from there..

This is the only way I know - is extremely quick - and only works for calling back a single value. As it needs its own connection to run in, I don't know. Because adps work on the database directly, there may be a way - I rarely get to work on them these days.

@Kelvin,
Ok, Please take a look at the following,
https://www.experts-exchange.com/questions/28661918/Access-ADP-question.html
Thanks

Only a inline function is "optimal":

CREATE FUNCTION [dbo].[fnOrientLastNoteID] ( @Orientationid AS INT )
RETURNS TABLE
AS
RETURN
    ( SELECT    MAX(ID) AS MaxID
      FROM      dbo.OrientationNotes
      WHERE     OrientationID = @Orientationid
      GROUP BY  OrientationID
    );
GO

Select allOpen in new window

> If you think its possible to (...) calling the function directly without using access module),

Specify control source as
=DLookUp("top 1 dbo.fnOrientLastNoteID(" & [TxtOrientationID] & ")","sysobjects")

or include call to your function as one of the members of FROM in the record source of the datasheet.

...further optimization, by the way, would involve getting rid of the function at all and instead specifying the control source as

=DMax("id","dbo.OrientationNotes","OrientationID = " & [TxtOrientationID] )

@steSan,
You mean to get rid of the variable, I did that, thank you.

@Vadim,
First thanks for providing a way to retrieve value from UDF in Access.

FYI- re performance I tested with the following

Public Sub TestSpeed()
    Debug.Print Now()
    For i = 1 To 100000
        'z = CallfnOrientLastNoteID(Nz(i, 0))
        'z = Nz(DLookup("top 1 dbo.fnOrientLastNoteID(" & 0 & ")", "sysobjects"), 0)
        'z = tMax("ID", "OrientationNotes", "OrientationID = " & Nz(i, 0))
        z = DMax("ID", "OrientationNotes", "OrientationID = " & Nz(i, 0))
    Next
    Debug.Print Now
End Sub

Select allOpen in new window

and these are the results
1- took 1.5 Min.
2- took 3.30 Min.
3- took 3 Min. (this is a alternative to the build-in dmain functions I found somewhere, perhaps in AccessWeb).
4- took forever, I stopped running after few hours and it was holding by 30k...

> 4- took forever, I stopped running after few hours and it was holding by 30k...

Of course, because in this case the calculation is most likely being done in the client, i.e. Access probably retrieved all records and then calculated the maximum. You asked if it's possible to do without special efforts, and that's how. Whether to actually do it or not, depends on the amount of data and amount of time you save. If the data is small, then even 10 minutes of the saved time of the programmer  is more than that extra millisecond saved by the server. If the data is large, it's another story.

In your case I would still go without the UDF; instead I would create a function in Access similar to the accepted solution, but sql statement would extract the maximum directly rather than through the UDF:

public function MyMax(OrientationId)

MyMax= currentproject.connection.Execute("SELECT MAX(ID)  FROM  dbo.OrientationNotes where OrientationID = " & OrientationId )(0)

end function

Select allOpen in new window

and the expression in the textbox : =MyMax([TxtOrientationID] )

If you, further, create an index on the column ID, then it would be instantaneous.

...actually, I became interested and tried what happens if we use DMax - does Access really calculate the maximum at the client side. In my case it did not - according to Profiler, Accesss sent to the server proper statement select max(<column>) from <table>, so there should be no reason for such a slowdown. Finding out why you observed it would probably require the same monitoring with Profiler as I did, plus, possibly, also taking into account the versions of Access and of SQL Server - but most likely it would present only academic interest, if any.

so there should be no reason for such a slowdown

1- look at the following, I think you will agree with me that this behavior is a bug in Access 2003..
https://www.experts-exchange.com/questions/28655341/Access-2003-issue.html

2- Re your previous post, about avoiding UDF and have it in the Access call, I tried that test speed function with yours and that took exactly 3 min, then I tried again the first option with UDF, and it was about the same as previous time, 1.5 Min.

@bfuchs, no, I really meant optimizable. The optimizer can only handle this kind of functions well. Any other function is threated like a black box.

No, I wouldn't agree right away that this is a bug. To find out what exactly is going on, you really need to run Profiler on sql server and analyze the statements coming from Access. Otherwise, it's all gueswork and we don't know even whether the slowdown is in the server or in the client. Also, you need to try it on compiled project, ade, and preferably by runtime-only Access, because with development one (adp) Access may do things it wouldn't do with runtime, to assist the developer - such as, for instance, retrieve all information about all tables in the database, and more.

...just tried =dmax("indexed-column","<table with 15M rows>). Took ~0.3 seconds, as expected.

@steSan,
Testing your version of function & getting attached error when trying to execute the following

CallfnOrientLastNoteID = Nz(cnn.Execute("SELECT dbo.fnOrientLastNoteIDTest(" & ParameterToPasstoFunction & ")")(0), 0)

Select allOpen in new window

Untitled.png

@Vadim,

Actually the reason why I started this post is because my users (using ADE's in A2003) were complaining about the slow performance of a form displayed in datasheet mode, and I finally configured that the control causing it is the one with Dmax as its source..

see following post
https://www.experts-exchange.com/questions/28655341/Access-2003-issue.html
this is not happening to mu A2000 users.

that proves the following
A- its not a server issue
B- does not only happens in development environment.

Will let you know what I find out from analyzing profiler.

Thanks,
Ben

Hi
We've had a long weekend, so just catching up with this.

All domain functions (DMax, DSum, DLookup) are Access ONLY functions. They get evaluated in Access, not SQL Server. In an adp (or ade) you're working directly on the tables, but these built-in functions are evaluated in Access, so the data will be retrieved. In not aware of anything in an adp that can convert a DMax into a SELECT Max(xxx) type of SQL statement. VBA is just VBA, it doesn't do any under the hood stuff differently for adp's.

So you are left to use a SQL Server function (or similar) to resolve. The time taken will depend on indexing as mentioned above.

Kelvin

> In not aware of anything in an adp that can convert a DMax into a SELECT Max(xxx) type of SQL statement, it doesn't do any under the hood stuff differently for adp's.

Apparently it does, all you have to do to see it is to run Profiler.

Attached you can see what it does for each call of dmax() in my loop.
Do you see any abnormalities?
Untitled.png

No, I saw absolutely the same in my case. Those declare @p1... lines are what I meant by Access retrieving extra information from the database in order to assist developer, and unfortunately, I saw them also in runtime mode (i.e. Access Runtime running ade). They increase execution time by some negligible ms. The good part is the statement select max(id), which is the optimal way to do it. Now all you have to do is to have an index on the column "ID".

For you the interesting part is what is cut off on your screenshot on the right, i.e. cpu time and reads. Now you can compare your ways 1-4 in the comment 40745313 above.

@Vadim,

(sorry for the delays but the only time I get to see only my output in profiler is at night when nobody is using it, I tried filtering the trace by host name, but looks like there are other people with the same name..perhaps you know a better way to distinguish between users)

A- the diff between the 1st option and the 4th, is that the first only has one command in profiler SELECT dbo.fnOrientLastNoteID(2) with 6 reads, while the Dmax has two
1-declare @p1 int
set @p1=210
exec sp_prepare @p1 output,NULL,N'SELECT Max(ID) AS C1 FROM "OrientationNotes" WHERE (OrientationID = 2)',1
select @p1 3 reads
2-SELECT Max(ID) AS C1 FROM "OrientationNotes" WHERE (OrientationID = 2) also 3 reads
re cpu, all of them had 0.
this is whats happening in dmax running from 2003.
when running it from Access 2000, the two statements are the same except for..
1- the order of them are opposite
2- only the first has 3 reads (which is SELECT Max(ID) AS C1 FROM "OrientationNotes" WHERE (OrientationID = 2)), while the second is 0.

B- I ran today my test on the 2000 pc, basically all were more or less the same, except for dmax, this one took 15 to execute 30k while in 2003 it took at least 2+hours..


@All Experts,
I am trying to implement the in-line solution suggested by steSan, however I'm getting the following error when running this in SSMS

select dbo.fnOrientLastNoteIDtest(277796)

Select allOpen in new window

Msg 4121, Level 16, State 1, Line 2
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnOrientLastNoteIDtest", or the name is ambiguous.
Does someone knows how to call it in SSMS?
PS if thats kind of complicate issue I will post another question..

Thanks,
Ben

I don't believe you can use a function in SSMS like that.

You could try some T-SQL like

DECLARE @MaxVal    int

SET @MAXVAL = dbo.fnOrientLastNoteIDtest(277796)

Kelvin

@Kelvin,
tried that and got the same error message.
Actually that works for my original function, but looks like in-line function since its returning a table must have a different calling method..

AH, an table function

SELECT *
FROM dbo.fnOrientLastNoteIDtest(277796)

might just do it

Kelvin

OK in SSMS that worked, however when I tried modify your function above to the following

CallfnOrientLastNoteID = Nz(cnn.Execute("SELECT * from dbo.fnOrientLastNoteIDtest(" & ParameterToPasstoFunction & ")")(0), 0)

Select allOpen in new window

I get the attached.
Untitled.png

@Kelvin,
Actually I see that problem only happens when the function does not return any values.
I wonder if I need to change the your function in FE to handle that or perhaps the in-line function posted above (ID:40741819) needs to be modified?

As a bottom line, I'd do what I posted in ID ID: 40745338, to avoid any overhead Access is trying. Plus, once again, make sure you have an index on the field "ID". With that, it shouldn't be taking more than a fraction of second.

@Vadim,

How did you figure out what I was in middle testing...?

1-without the in-line function, just using a regular UDF, the method Kelvin suggested proved to work much faster, see my results comparison above.

2- But that turns to be different when using it to call the in-line table function as suggested by steSan.

3- However I am having the same issue when using your method to call in-line function as posted above.

4- ID column is indexed.

I'm a bit puzzled as to why you'd use an inline function to return a single value. That appears to be at cross purposes. I'd use the inline function to return a subset of a table or query - think of them as views with input parameters. Generally, I create those to replace frequently used joins in SQL, so I can just call the in-line function and join it just like a table.

But to get a single value - no a scalar function is what is needed there.


Kelvin

This thread is already quite long, so even figuring what you mean by "see above" becomes a little nontrivial. Anyways, if the column is indexed, then running select max(column) from table should be absolutely instant. You need to confirm that outside of Access, in SSMS. Once it works, any of these many ways to run this SQL statement from Access (by calling UDF, by calling dmax, by calling execute(select), and so on) should also be instant. There's no need to open separate connection in order to run a single statement, given that you already have currentproject.connection; there's no need to create separate UDF for a single trivial SQL statement; but then even doing all that shouldn't add more than couple more milliseconds.  If you are getting those minutes that you mentioned, it probably means some other, more global problem to investigate; maybe you need to UPDATE STATISTICS. There's absolutely no room for any minutes in this no matter how you run it.

@Kelvin, Vadim,

You're right, in my latest testing I did not see significant difference between calling the UDF and the in-line function, (neither did I see anything different by opening a connection or by using only the currentproject.connection), therefore I guess its not of such a high importance to get that in-line issue resolved here..

Perhaps in another instance where I need to use table results, I will then post a question re this.
For the time being, I am glad that this worked out, and very thankful for your help!!

@Vadim,

by calling UDF, by calling dmax,

With this latest I disagree, as this was actually the reason I started this whole work as dmax is comparing to all the other options "a real disaster"..In case you can help me figure out why and how to fix that (as I do use it in other areas of my app), I would start a new post so you can be awarded properly.

Thanks,
Ben

sure, let's try, post the link when you opened. How about remote access?