Access Database Limitations

Access was designed as a simple database to be used by non-programmers.  As the needs grow though, it’s often necessary to upgrade the database beyond what Access can support.  Here are some of the limitations that users often run into forcing them to migrate away from Access.

Concurrent Users

Even though Microsoft has stated that Access is able to support 255 concurrent users, it is a more practical choice to select Access when the database will be used by only 15 to 20 simultaneous users. If more than 20 users try to gain access to the database, performance and response time may begin to suffer.

Internet Limitations

Microsoft Access does have a few limitations. Access was not designed to be used with applications that are web based since its forms and reports are only functional in a Windows environment and are not compatible with an internet browser like Internet Explorer. The Access Jet database that is used by Microsoft Access is a file based system and does not contain the server features that are available in SQL Server, Oracle or other ODBC compliant databases. Access databases are more suited for web based solutions when the number of users is small or when the users are just viewing data rather than editing or deleting.

Remote Access

In the past, record locking activities in Access caused an increase in response time which resulted in slow performance on a VPN (Virtual Private Network) or WAN (Wide Area Network) when accessing the Access Jet database. However, this problem has been eliminated as a result of quicker broadband connections. It is important to note that performance can also be enhanced if a continuous connection is maintained to the back end database throughout the session rather than opening and closing the sessions every time a table is requested. If you find that Access database performance over a VPN or WAN connection continues to suffer, then perhaps Microsoft Terminal Services is an effective alternative. We have found that Access databases linked to a SQL Server or Access Data Projects work quite well over VPN and WAN connection. Please contact us and let us know how we can help.

Access Migration Benefits

Get to the Cloud Faster

Leverage our established blueprints, solutions and automation expertise to quickly move to the cloud and achieve a faster time to value.

View Details

Reduce Migration Risk

Our security posture and DevSecOps approach means getting to the cloud without disruption to your current operations while ensuring compliance and governance.

View Details

Increased Collaboration

When your teams can access, edit and share documents anytime, from anywhere, they’re able to do more together, and do it better. Cloud-based workflow and file sharing apps help them make updates in real time and gives them full visibility of their collaborations.

View Details

Data Quality

When you make the move to cloud computing, all files are stored centrally and everyone sees one version of the truth. Greater visibility means improved collaboration, which ultimately means better work and a healthier bottom line. If you’re still relying on the old way, it could be time to try something a little more streamlined.

View Details

Not all Access database features are compatible with SQL Server. For example, SQL Server and Access have different sets of reserved keywords. Issues such as these can prevent a successful migration to SQL Server.

 

Here’s a few of the issues that can come up that will prevent you from a successful migration.

  • Access tables do not have unique indexes.
  • Access tables have hyperlink columns.
  • Access tables have attachment columns
  • Access has validation functions that cannot be converted to SQL Server