Performance tuning using MS Access and SQL

If you are interested in the fastest performance, don't use Access as a front-end to a SQL Server database. While Access is relatively easy to learn and fast to develop in, its performance is poor when compared to other front-end options. But if you like to develop in Access, or don't have any choice, then the tips on this page will help a little to boost your application's performance. 

When you write a query in Access that needs to run against a SQL Server database, Access will locally evaluate the query, looking for any clauses or expressions that cannot be run on SQL Server, and those that can. It will do its best to run as much of the query, if not the entire query, on SQL Server rather than locally. This is because SQL Server can run the queries much faster than Access can. If Access cannot run the entire query on SQL Server, it will run as much as possible on it, and when it retrieves the results set from SQL Server, it will then complete the query locally.

Click here for more....!