Access ADP question

Actually, I tried changing for the following and it seems to work in both versions.
Set cnn = CurrentProject.Connection

However I am wondering if someone has a way to call that UDF directly without using Access modules, or perhaps using a SP instead of an UDF (which is accessible via the Access database container)?

Can you please give some sort of indication as to how you want to use it. In an adp, you can call the functions via views etc... So not sure where the problem is.


Kelvin

If you were to set the connection detail in the vba module you're working on then you could set the variable "on the fly"

That is set these in the module you use

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.AccessConnection

Along with the variable you want populated
i.e.
Dim varMax as integer

Then in the place you mneed it

Have

varMax  = Nz(cnn.Execute("SELECT dbo.fnMySQLFunction(" & Me!ParameterToPasstoFunction & ")")(0), 0)

This would avoid the need to pass of to a function - although I don't see the reason why you couldn't.

You could even set a field in a form this way.

Kelvin

PS You should read carefully the comments from @bitsqueezer above - they are very good and comprehensive.

Hi Experts,
I didn't expect this topic to get that deep and comprehensive..

@Kelvin,
My point here is not to shorten the code, its just that I am assigning it to a control in a datasheet form with numerous records, therefore just guessing It would be better for performance if Access can directly communicate with the server without the use of code.

P.S, Your solution in the other post is so far working excellent, see my latest comment there.

@BitSqueezer,
There is no doubt that you won the contest as the best writer...(I always enjoy reading your comments, as a matter of fact, sometimes in my spear time I filter for your postings in EE and read them, excellent talent tough..).
Keep going with your extra ordinary contribution!!
Thank you!

Hi Ben,

thanks, I only tried to give you all informations you need to find the best solution for your purpose with the fastest performance and with keeping an updatable query. But it seems you prefer to use the frontend instead to get the data..:-)

Cheers,

Christian

Hi Bit,

I did not mean to minimize the importance of your highly useful write-up on this post in either way, perhaps the opposite..

However re what way I should take to accomplish that, I need to do some more modifications to that function in order to get that called as part of the view, since the OrientationID parameter is not part of the table used there, Guess will do more testing & let you know.

For the time being, I am happy that Kelvin showed me a way out of the Dmax in Access, as in my version(2003) it was really a disaster, something that can be accomplished in 1.5 Min its taking hours with dmax (see results of my testing in issue  https://www.experts-exchange.com/questions/28659574/How-to-call-a-sql-custom-function-from-an-Access-project.html?cid=1752).

Now the question is just to further optimize, that its already not such a high priority, waiting to get okay from manager to go ahead on that..

Thanks,
Ben

Hi Ben,

no problem, it didn't felt as you would have meant to do so.
The trick with the Sub-SELECT is that the value you get can come from anywhere, so there is no big difference as if you would insert a DMax into an Access SQL SELECT fieldlist, which is nothing else than a Sub-SELECT. In fact, the same trick works in pure Access, too - you can query a table which is updatable and add a DLookup or DMax etc. to the field list to get an additional read-only value and at the same time keep the result updatable.
But as we fortunately don't need Access SQL in an ADP it's best to keep all the data processing on the server. Should be nothing more than a simple change of the view you use for the form.

As always: It's only a recommendation, you must decide on your own which way fits best for your need... :-)

Cheers,

Christian