In case if database will migrated. Can I expect that it will work faster?
Jet and ACE are very fast and are optimized for working with Access. Just moving the data to SQL Server will almost certainly result in a much slower application. When I develop applications, I always use good client/server techniques. So, even though the app may run for a couple of years using Jet/ACE before the size or the number of concurrent users dictates a conversion, I can convert in an afternoon in all cases unless I run into some data issue. It can even take less time but there is a lot of testing that has to take place to ensure that every form/report/query/piece of code is working as expected with the new BE. And the converted app will be as fast or faster.
WHY? planning. Access applications are frequently built by amateur's who use macros and sloppy code (if they write any) as well as local filtering. When your form is bound to a table or to a query without any selection criteria, it is like using a straw to suck down all the rows from the server. The form is displayed as soon as enough data has been retrieved to fill it but then Access just sits back and sucks and sucks and sucks until ALL the rows from the table or query are local and this is EXACTLY what makes the converted app so slow. The point of using a server based BE is to restrict dramatically the amount of data that is dragged over the network. Therefore, all your forms/reports need to be bound to queries that have selection criteria that severely limit the number of rows requested. In the case of forms, preferably to ONE.
So, instead of bringing all the data down and letting the user filter it locally, you get the user to determine what he wants to see and use criteria with queries to select only what he needs. I use two kinds of "filtering" methods. One is a form with all the criteria options. The "run" button assembles all the criteria into a where clause. It runs a dCount to determine how many records would be selected. If the answer is ONE, the search form opens the main edit form to the one record selected. If the answer is too many, I give the user an option to go back and select more options before returning the data. Then for the not one case, the app opens a non-editible list form with the most appropriate fields for letting the user select a record. Then the double-click event on the ID of a row in the form opens the standard main edit form for the one record selected.
If you have code with loops whether they be VBA or macros, you need to clean them up to make sure you are not executing instructions inside the loop that should be executed outside the loop.
In order to know how bad your situation is, make a few backups and then attempt to do a straight upsize. Run the app. Start working on speeding up the slow parts.
There is no reason to start by converting all your querydefs or embedded SQL to T-SQL and stored procedures. That is the last resort. Once you clean up all that you can with the way the forms work and any batch processing that uses loops, you can start by trying to optimize the queries. You will need to look at joins and search criteria and indexes to determine if they are appropriate. Sometimes creating a view on the server will ease the pain with a commonly performed join or calculation like concatenating the first and last names into one field.
