I'm advising a small non-tech company that outsources their software, hardware, and support services to a local IT provider. This company is preparing to migrate several of their internal applications from an in-house managed server to a cloud-based provider.
One of these applications is a business-critical MS Access-based inventory management tool. This tool is unfortunately built and maintained by one person located overseas, so we expect very little support for any custom development and have to treat the application as a black box.
Sales employees are currently accessing the in-house Access application via VPN and virtual terminals, connections to which have been EXTREMELY unstable. Additionally, the application can hang for up to half an hour intermittently, which has gotten worse over the 2 years they have been using it. One of the primary goals of this cloud migration are to solve these performance/stability issues.
Final note: their IT provider is looking to use Anchor as a cloud vendor.
My question: How can we best resolve these performance issues by moving "to the cloud?"
Best Answer
The "best" solution which we found was:
1. migrate the Access tables to an SQL tables 2. Migrate view & stored procedures to the SQL server 3. make the MS-Access run from a network location. This way there are two Ms-Access: one for the development work, the other for everyday use. This way ms-access can scale.
As the developer who has done this I can tell you this:
Migrating the tables is pretty strait-forward (there's even an option for that in Access). Then you need to check that the data types have stayed the way the are supposed to. SQL might use a data size which is bigger then it should be (currency shouldn't be money, it should be decimal (18,2); avoid floats if you don't need to; use the smallest data type possible; strings have migrated correctly; etc). Also: when Access uses SQL as it's back end it can only have one column as a primary key, otherwise it doesn't link properly. So if you had a primary key which was composed of two columns, you need to add to that table an identity column.
Once that is established you have a split Access application which means better availability. Yet, you need to migrate the views and stored proc: basically copy & paste the code form Access to SQL. Since the syntax is different you need to check for the following pitfalls:
(1) for example, now there's IIF in SQL so that sort that part out.
(2) parameters which are incorporated in the stored proc are used in a different syntax in SQL so that's also something to be aware of.
(3) Avoid ORDER BY in views. (don't use the SQL option, just don't use it and do the sorting in Access)
(4) Last but not least: in Access the "true" is -1, in SQL it is 1. It is very important to check that the WHERE clause says WHERE flag <> 0, and not flag = -1. Good luck!