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

Hi Experts,

I created the following function in SQL, how can I assign to a control in a Datasheet form in my Access project to get the returning value of this function?

I am trying the following and getting a #Name?
=[dbo].[fnOrientLastNoteID]([TxtOrientationID])

P.S. In case you how to optimize this function, I would appreciate that too..

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


	RETURN @Result;

 

END

GO

Open in new window


Thanks
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
see:

http://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

Open in new window

bfuchsAuthor Commented:
@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.
http://www.experts-exchange.com/Database/MS_Access/Q_28655341.html

Thanks,
Ben
bfuchsAuthor Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Mike EghtebasDatabase and Application DeveloperCommented:
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.

Open in new window

bfuchsAuthor Commented:
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
bfuchsAuthor Commented:
@eghtebas,
Any hope for us?
Would appreciate if this can be done today, as I am not coming in to work tom.
Thank you.
Kelvin SparksCommented:
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
bfuchsAuthor Commented:
@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?
Kelvin SparksCommented:
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
bfuchsAuthor Commented:
See attached
Untitled.png
Kelvin SparksCommented:
AccessFunctionName= cnn.Execute("SELECT dbo.SQLFunctionName(" & ParameterToPasstoFunction & ")")(0)

Appears to be meiising a space - try above.


Kelvin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
That worked, thank you!
bfuchsAuthor Commented:
@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..
Kelvin SparksCommented:
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.
bfuchsAuthor Commented:
@Kelvin,
Ok, Please take a look at the following,
http://www.experts-exchange.com/Database/MS_Access/Q_28661918.html
Thanks
ste5anSenior DeveloperCommented:
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

Open in new window

Vadim RappCommented:
> 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.
Vadim RappCommented:
...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] )
bfuchsAuthor Commented:
@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

Open 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...
Vadim RappCommented:
> 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

Open 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.
Vadim RappCommented:
...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.
bfuchsAuthor Commented:
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..
http://www.experts-exchange.com/Database/MS_Access/Q_28655341.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.
ste5anSenior DeveloperCommented:
@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.
Vadim RappCommented:
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.
Vadim RappCommented:
...just tried =dmax("indexed-column","<table with 15M rows>). Took ~0.3 seconds, as expected.
bfuchsAuthor Commented:
@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)

Open in new window

Untitled.png
bfuchsAuthor Commented:
@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
http://www.experts-exchange.com/Database/MS_Access/Q_28655341.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
Kelvin SparksCommented:
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
Vadim RappCommented:
> 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.
bfuchsAuthor Commented:
Attached you can see what it does for each call of dmax() in my loop.
Do you see any abnormalities?
Untitled.png
Vadim RappCommented:
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.
bfuchsAuthor Commented:
@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)

Open 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
Kelvin SparksCommented:
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
bfuchsAuthor Commented:
@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..
Kelvin SparksCommented:
AH, an table function

SELECT *
FROM dbo.fnOrientLastNoteIDtest(277796)

might just do it

Kelvin
bfuchsAuthor Commented:
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)

Open in new window

I get the attached.
Untitled.png
bfuchsAuthor Commented:
@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?
Vadim RappCommented:
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.
bfuchsAuthor Commented:
@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.
Kelvin SparksCommented:
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
Vadim RappCommented:
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.
bfuchsAuthor Commented:
@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
Vadim RappCommented:
sure, let's try, post the link when you opened. How about remote access?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.