Upsizing Completed - Now What? (1 Viewer)

Using the SSMA and much help from this forum I have successfully upsized a large customer application from an Access 2010 split database to Access 2010 front end and SQLServer 2014 back end. I use ODBC to connect FE to BE and it all seems to work, and user's won't even know it's happened.

So it's working but I'm wondering if I should be thinking about making changes to make it faster, better(?), more robust, more efficient etc etc. I'm not seeking full answers with code, but just some recommendations of what I should be reading/googling.

I see terms used like pass-through queries, stored procedures etc but I've no idea whether they might apply to me. Please bear in mind that this is a large application with around 100 tables and at least 2500 places in the code where the word "openrecordset" appears!!

Thanks
Dave

There are a few things to look out for. For example, features that depend on row order may not work the same way; uniqueness constraints and foreign key constraints behave differently with null values; cascading referential integrity is different. If you are using bound forms then consider making them unbound. Make sure queries are parameterised properly using variables wherever possible.

If your database hasn't been designed/redesigned specifically for SQL Server then review the database implementation. Check things like whether the SQL Server datatypes are the appropriate ones and that the right keys and indexes are in place.

Make sure you have the right recovery model, backup strategy and permissions set up.

The most important thing you can do is: test, test, test. Your testing plans ought to include load testing because any migration to a new DBMS can expose performance and resource contention (blocking) issues that you didn't see before.

I don't mean that to sound too daunting. Most migrations will probably go just fine, but having put in the work to upgrade you don't want users to have a bad experience.

Pass-Through Queries - there is no need to do this for around 80% of the code.
If you use standard SQL Statements (e.g. Select bla where gla > 5...)
the ODBC layer will actually translate that to TSQL.
If the query is in a Form, for Access 2010, Microsoft was rumored to do great things for the ODBC translation too. All getting set up to use AZURE with the ODBC driver known as SQL Server Native Client.
Now, if your query uses custom Access VBA functions the TSQL translation will work, but it will not be near efficient. The standard SQL functions such as Left, Trim, CInt, ... will translate fine.

Here is a MUST READ site with two very respected members with two very different points of view... both are valid because I am not worthy to judge.
Be sure and add some Thanks, this is some good points of views that I couldn't find anywere else on the web.
http://www.access-programmers.co.uk/forums/showthread.php?t=246512&highlight=passthrough

So... we all agree to avoid the Access Format function as you will see.

In my expereince, the biggest gains from adjusting code for SQL Server backends comes from changing the way subforms are populated.

Static RecordSource and LinkFields are many times slower than using recordsets returned by parameterised stored procedures run by the OnCurrent event of the main form.