workin on a project with access frontend and sql server backend. quite several of my queries use vba functions as in IIf and sum user defined function too. As far as i know those queries are not handed thru to the sql server coz it cannot deal with it. So far so good. But i'm experiencing serious connection problems using those queries. Could there be any problem arising from vba enhanced queries on sql server tables other than a sumwhat slower performance and a higher net traffic?
Iif should not be a problem but vba UDFs will be (I presume sum = some?).
Access will pass as much of the processing to the BE as it can but when it comes to UDFs it has no option but to request the data and process it client side. You can improve performance dramatically by porting as much of the data retrieval code to SQL server in the form of stored procedures, views and functions.
The only problems specific to what you suggest might be time out problems... What specifically are you observing?
that fits my idea of it. the thing is i hav a function which is called getQPar for query parameters as the standard access parameters are annoying like ..
i don't get a time out - i attached the error message (sorry it's partially in german)
As a sidenote, you would probably benefit from moving your functions and other database rules and logic to the physical database itself. There are very few VBA functions dealing with data only that cannot be replicated on SQL Server.