some of mvps like Richard Rost recommend to avoid aggregate queries and dlookup functions in splitted (sic) database.
then what are alternative of these important features. please guide.
Context is everything. Just because you think this is what Richard is recommending, doesn't mean it is so. I'll give him the benefit of the doubt and assume you have taken the recommendation out of context. Perhaps he'll jump in and clarify.
Access is unlikely to perform well over an unreliable LAN so if you can't upgrade the LAN AND use ONLY wired, not WiFi connections, your best option is to convert the BE from ACE to SQL Server. If you build your application properly to take advantage of having the server do the heavy lifting (your app is almost certainly not designed that way now and so will require design changes), your application won't get slower and it might get faster but your data will be much less susceptible to corruption because SQL Server is better at protecting the data than Jet or ACE.
1. There are almost always better solutions than domain functions but if you are using them correctly, then they shouldn't be overloading the LAN. You should never use domain functions in queries or inside code loops because each domain function runs a separate query. So, if your query selects 2,000 records, Access has to run 2001 separate queries. The larger the recordset, the slower the outer query will be. Same for a VBA loop. If the loop is processing 10 records, you can be as inefficient as you want but if it is processing 100,000 you have no leeway for poor design choices. For single records in a form they are no problem. The best substitution for a domain function is usually a left join in your query. If you can't find a way around using the domain function, you might try using a custom version such as what arnelgp linked to.
2. If you need to aggregate data, you need to aggregate data. PERIOD. Telling you to avoid doing so is idiotic and like telling you to avoid water because you might wet the bed at night.
