Access ADP question
Hi Experts,
I have the following Access function, that is used to execute a SQL UDF, that works fine in Access 2003, however in A2000 I am getting the error attached, when executing the line "Set cnn = CurrentProject.AccessConne
I have the following Access function, that is used to execute a SQL UDF, that works fine in Access 2003, however in A2000 I am getting the error attached, when executing the line "Set cnn = CurrentProject.AccessConne
Public Function AccessFunctionName(ParameterToPasstoFunction) As Long
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.AccessConnection
'Set cnn = New ADODB.Connection
'cnn.ConnectionString = sConnect '- this is the connection details set elsewhere for connecting to the database
'cnn.Open
AccessFunctionName = Nz(cnn.Execute("SELECT dbo.fnMySQLFunction(" & ParameterToPasstoFunction & ")")(0), 0)
cnn.Close
Set cnn = Nothing
End FunctionASKER
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
Kelvin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.AccessConne
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!ParameterToPasstoFuncti
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.
That is set these in the module you use
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.AccessConne
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!ParameterToPasstoFuncti
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.
ASKER
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.
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.
ASKER
@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!
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
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
ASKER
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
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
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
ASKER
Set cnn = CurrentProject.Connection