|
|
 |
|
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. |
|
|
|
|
|