HomeServicesTechnologyAnalyticsOur ClientsAbout PCAContact Us

Outgrown Your Legacy Excel & MS Access Application?

Below are the ten most common reasons why our Clients decide to migrate to a SQL Server-based Smart Client solution.    PCA will look carefully at your existing application(s) and business needs, and help you formulate a logical, economical approach.
 

Too Many End Users for MS Access and Excel

MS Excel and MS Access applications are simply not designed to support more than a handful of End Users.    In a multi-user environment, both MS Excel and MS Access will quickly turn from an asset into a liability, where costs and issues associated with performance, versions, data integrity, etc. will steadily increase.  For example, MS Access is limited in the number of concurrent users it can support at any one time (8 concurrent users, before a performance governor kicks in), and the amount of data it can store (~1 GB). 

Concurrent Internet Access

MS Excel and MS Access applications are not designed to expose to the Internet.    Workarounds are available to "Internet enable" an MS Access application (Citrix or VPN with Remote Desktop are common), but these approaches provide poor performance, cost money to build and maintain, and only serve to place additional multi-user burden on the application.  Remote access is OK as a stop-gap measure, but not a sustainable approach.

Poor Data Integrity in MS Access/Excel

MS Excel and MS Access provide little to no means to enforce proper integrity of business data — the assurance that your business data is both accurate, complete and follows a consistent format.  With a true relational database, data integrity rules and constraints are built into the database, and are enforced at 'data entry time' in the application to insure that all data in the application is complete, and all fields conform to a consistent, valid format.  Absent proper data integrity, calculations or references to other data within the application will not function properly, and reports that are used to make business decisions are suspect or even useless.

Slow Application Performance in MS Access/Excel?

The root-cause of poor application performance is due most often to deficiencies in the underlying database schema design.  This is true for any database application, including MS Access and MS SQL Server.   In addition, Poor MS Access performance might be a signal that your application is poorly designed; it can also signal that you may have outgrown the built-in limitations of the MS Access application.  If you are frequently compacting and repairing a corrupt MS Access system, it is time to refactor your application design, or migrate to a SQL Server database.

Exceeding Database Size Limits in MS Access?

If you are pushing the data store limitations of MS Access, or you have run out of columns or rows in MS Excel, it is probably time you seriously consider moving to a real relational database to support your business data management needs.  While Microsoft advertises that MS Access can handle up to a 2 GB datastore, in reality you will notice appreciable degradation in performance and stability over 1 GB.  Independent of the size of the datastore, a large number of records and/or users can also exceed MS Access limitations.  Common work-arounds include deleting or archiving older data, but this prevents you from analyzing important patterns and trends in your operational and customer business data.

Security Deficient or HIPAA Compliance Required?

MS Excel and MS Access can only be protected by 2 things: a password, and the operating system itself e.g. security attributes set by the network administrator at the network hard drive and directory levels.  MS Access provide very limited means to establish, manage and validate security and true End User authentication.   In addition, many businesses require that different people inside and outside your organization have different data management privileges associated with individual business roles i.e. the ability to view and modify certain types of information.  SQL Server provides much more robust security capabilities, and supports true role-to-privilege-based security management controls.

MS Access Reporting Inflexible / Inadequate?

It is quite common to have 85 different reports, 11 of which are actually used, and even these are deficient or not trusted.  If you have a simple application with 6 or so tables, with a limited sub-tables and less-than-complex relationships among the various data fields, MS Access may be perfectly suitable to your reporting needs.  MS Access also makes it easy to design new reports, and perform ad hoc queries.  If your reporting needs require more complex data modeling and formatting, or more robust nested queries, or your reporting need to be high performance and 'bullet-proofed' for data integrity, then SQL Server Reporting Services offers the right approach.  Dynamic Grids controls embedded directly into your application, and SQL Server Analysis Services both go well beyond the capabilities of canned reports, and are well-suited to 'ad hoc' reporting and more advanced business intelligence needs.

MS Access Application Unreliable?

Does your application run like a work horse 24x7?   Or, does it crash frequently or become corrupted, causing employee downtime and/or lost or corrupt data?   The reliability of an MS Access application is constrained primarily by the quality of application design (poor application design and development decisions), and furthermore by the built-in limitations of the MS Access application itself.   If you are frequently compacting, restoring or repairing a corrupt MS Access system, it is time to refactor your application design, or migrate to the SQL Server database.  PCA can help you understand whether remedial engineering of your MS Access application can improve reliability, or whether migrating to SQL Server makes more sense.

Growing Workflow Complexity?

MS Excel may be well-suited to supporting your application needs, provided the workflow required to support your business process is fairly linear, and the information model is straightforward i.e. many interdependent relationships among discrete pieces of information are not required to support your business process or reporting needs.  Complex business processes and associated workflows is the primary reason that companies develop custom applications.  SQL Server, in combination with a .NET Smart Client front-end application, provide a number of efficient and easy-to-use ways to model complex business processes and workflows.

Lack of Industry / Regulatory Compliance?

Specific attention to data security and application design and development processes and associated documentation is required to comply with a number of different industry and regulatory standards including SarBox, SAS 70, HIPAA, 21 CFR Part 11, ISO and others.   For example, any application that contains patient data in MS Access is de facto non-HIPAA compliant, due to deficient MS Access data security capabilities.  A SQL Server based application is required to meet many minimum industry and regulatory standards, and can be much more cost-effective in the long run.
  

Original Developer Unavailable or Under-Experienced?

We also see this situation quite often — a business with 75 employees running its day-to-day operations on a patchwork combination of MS Excel and MS Access applications that are cobbled together with poor engineering methods, and 2-3 individuals who are responsible for "holding the system together," solving constant end users' problems, and producing custom reports for management.  Employee downtime is commonplace, and important business data is often unreliable or missing altogether.  If this situation sounds familiar, it is probably time you consider migrating your system to SQL Server.

      (617) 527- 4722 x122

Get Started Special Offers

MS Access to SQL
Server Migration

MS Access to SQL Server Migration
Simple, inexpensive method to move your MS Access data into SQL Server, to improve stability and capacity.  

More ...
Click here to Request that PCA Contact Me

SQL Server
Structure Audit

SQL Server Structure Audit
Experts analyze your SQL database design and/or coding deficiencies that impact performance, reliability, data integrity, with remedial engineering recommendations. 

More ...
Click here to Request PCA Contact Me