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.AccessConnection".

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 Function

Open in new window

untitled.bmp
LVL 5
bfuchsAsked:
Who is Participating?
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.

bfuchsAuthor Commented:
Actually, I tried changing for the following and it seems to work in both versions.
Set cnn = CurrentProject.Connection
0
bfuchsAuthor Commented:
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)?
0
Kelvin SparksCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BitsqueezerCommented:
Hi Ben,

as I saw in your recent questions:
http://www.experts-exchange.com/Database/MS_Access/Q_28659574.html?cid=1752
http://www.experts-exchange.com/Database/MS_Access/Q_28655341.html

you are trying to execute a UDF to replace DMAX and similar functions.

Here are some hints for you:
In an ADP you are working directly with T-SQL so there is no possibility to include any local resources like a field name on a form.
If you use "=" this will execute only local functions, built-in or VBA, but will not execute any SQL. So you could run the function above and add it with "=" to a text field.
The only controls which are able to execute SQL directly are list controls like listbox and combobox which have a RowSource and a Recordset property. You can either use a SQL command in the RowSource property (not recommended as you need to search in row sources if you ever need to change the SQL) or execute a SQL command in VBA, get the recordset back and assign it to the Recordset property of the list control (the same is possible with forms).
The only way to mix local and remote resources (without VBA) is the form itself: Here you can set a stored procedure as RecordSource and use the InputParameters property where you can use something like "@ID int = Forms!MyForm!MyInputField". As that are separated properties Access can assemble both to a valid SQL Server T-SQL string to be executed remotely and the result will appear in your form. But also here you can of course execute the SP with VBA and assign the ADO recordset to the form's Recordset property as long as you are using SQLOLEDB as driver and adUseServer as cursor type to get an updatable form.
Using "=" in any newer version of Access since, I would say, A2003 is always slow, especially if you call a heavy weight function like the DMax, DCount etc. functions as it will need to open a connection, check the parameters, assemble a SQL command, run the command remotely, get the result back, close the connection - for each row. Access calculates all "=" assignments of controls at a late point of time so you will always see a delay of sometimes several seconds. That's the reason why you should avoid to use them as normally they can in most cases be replaced by adding them to the record source so they will be calculated remotely and you see the result immediately.
"But on that way my form is no longer updatable!" - that's wrong, it only means you didn't wrote the SQL command in a way that it's being updatable anymore. Here is how to do that:

There are two possibilities to keep a SELECT updatable:

1. Using Sub-SELECTs


This should be always updatable:
SELECT MT.ID, MT.A,MT.B,MT.C, 
       (SELECT MAX(ID) FROM dbo.MyOtherTable AS MOT WHERE MOT.ID_F = MT.ID) AS MaxOfMOTID
FROM dbo.MyTable AS MT

Open in new window

No need to use a UDF and the result can be used in the form where only "MaxOfMOTID" is read-only and the other fields can be changed in the form - Access handles that automatically.

2. Updatable JOINs


This should also be updatable:
SELECT MT.ID, MT.A,MT.B,MT.C, MOT.ID
FROM dbo.MyTable AS MT
INNER JOIN dbo.MyOtherTable AS MOT ON MOT.ID_F = MT.ID

Open in new window

This also works if you use a CTE or LEFT/RIGHT JOIN, the trick is: You need to include all PK fields in the SELECT, not the foreign key fields (which can be added also with alias but doesn't make a difference). If you do that, you can edit both tables in an Access form as long as you only change fields of one table at the same time. In case of LEFT/RIGHT JOINs you can only edit the fields of the JOINed table(s) which exists, in opposite to pure Access databases Access will not insert a new record in the JOINed table(s) automatically.

3. Updatable tables with table-UDFs


The thing with step 2 is that it would not work with a JOINed table that uses an aggregate function like MAX as you normally have no PK anymore in an aggregated query. But you can use a UDF to do the job: Create an Inline Table-Valued Function which returns a MAX value for each foreign key like this:
CREATE FUNCTION dbo.fnMaxOfID (@intID AS int)
RETURNS TABLE
AS
RETURN
(
   SELECT MAX(ID) AS MaxOfID
   FROM dbo.MyOtherTable AS MOT
   WHERE MOT.ID_F = @intID
   GROUP BY MOT
)

Open in new window

Now you can add that to the SELECT like this:
SELECT MT.ID, MT.A,MT.B,MT.C, MOT.ID, FN.MaxOfID
FROM dbo.MyTable AS MT
CROSS APPLY dbo.fnMaxOfID(MT.ID) AS FN

Open in new window

CROSS APPLY works like INNER JOIN, if you want to have the same as LEFT JOIN then use "OUTER APPLY" instead.
You can now change the fields of MyTable and MaxOfID is read only.
Using this way your UDF can return some other aggregates also which you can then simply add to the SELECT field list which makes the code easier to read than adding one SUB-SELECT for each aggregate (this would need to run separate queries for each further SUB-SELECTs whereas the UDF will only run once and then it will be applied to the result set of the table also only once).
By the way: You should also use that method if you want to use conditional formatting in your frontend: Don't create complex CF expressions in Access, let them be calculated in the backend and add the result always as a simple bit field which returns 0 or 1 so you can always use a simple CF expression which asks for the value of this calculated field. Gives the maximum CF performance in the frontend.

Always create a view or stored procedure in the backend (better performance is using a view and assigning the name of the view to the frontend form RecordSource property, use ServerFilter to assign WHEREs, use OrderBy properties to assign ordering and don't use ORDER BY in a view). That's the really fastest way of using Access ADP, and also faster than Access ACCDB/MDB as far as I've tested. If you consequently use the backend for ALL kind of calculations you get the maximum out of SQL Server/ADP.

By the way: If you have calculations which apply only to a row, use calculated fields in the table definition and set them to "Persistent = Yes", you can also add an index to such field. Classic example is the order item row: A calculated field would be Price * Quantity. Can also be used to calculate conditions which you want to use as CF for the frontend like "CASE WHEN Price < 0 THEN 1 ELSE 0 END" (very simply example) to set a background color to red with CF.

Hope that helps you to find the best way for your purpose.

Cheers,

Christian
0

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
Kelvin SparksCommented:
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.
0
bfuchsAuthor Commented:
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.
0
bfuchsAuthor Commented:
@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!
0
BitsqueezerCommented:
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
0
bfuchsAuthor Commented:
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  http://www.experts-exchange.com/Database/MS_Access/Q_28659574.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
0
BitsqueezerCommented:
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.