It’s not enough to upload your backend to SQL Server and expect all will be well with your application. In fact it may not be, as HiTechCouch states, Access may ask for too much data over the wire if the application is ill designed. It is for this reason I offer tips on my blog for optimizing the relationship between the two and advocate a revamp of your application to improve performance. You must look at every report, query and form and ask yourself, how much data does the user really need to see? Can you modify the row source property when launching forms and reports to only fetch limited row sets? Revamping the app may take a lot of time, but the rewards will be numerous in terms of performance. To learn more about SQL Server take a look at this post.
Optimize your SQL Server Database
Just uploading the database is not enough, you need to go through it and add indexes, constraints, time stamp fields and even change column types in order to optimize the database in SQL. Here again a knowledge of SQL Server is critical in order to leverage the engine in your design. For tips on how to design tables in SQL for Access take a look at this post.
Maximize the use of server processing
I encourage Access developers to learn TSQL and SQL Server in order to leverage the best of both worlds. I recommend to the extent possible having the server crunch through numbers and perform joins prior to fetching the data into Access. By using views and stored procedures you will offload processing time from Access onto the server, which will always be better equipped to handle most data situations better than Access. To learn about pass through queries that don’t tax Access click here.
Optimizing over a WAN connection
Using SQL Server over a WAN presents it’s own unique challenges. From record locking to query performance, you will need to optimize your code and objects to work over a wide range of Internet speeds. Here’s another post to help you get started.
Database Performance CAN be better with SQL Server
I can’t speak for HiTech’s experiences with SQL, but mine have been wonderful! After following the recommendations above I’ve seen BETTER performance with SQL Server over the WEB than with a local Access database on the user’s desktop! Think about that for a minute. The system performed better with a server located in another state than it did when it was a regular Microsoft Access database on the LOCAL HARDDRIVE.
What say you on this topic?