UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Limitations    
Limitations

This page is under consideration for merging with: Access Limits

Related Content:
    Access Limits


Contents

Is Access right for my project?

Often of concern is whether Access is the right development platform for a user's needs. And rightly so, as no one wants to devote hours of work to a project and then find out later that the chosen tool is not able to bear the load of their requirements.

Of discussion here are the inherent limitations of Access put forth up front, so you will not have to wonder whether or not you have made the right choice.

This article speaks to the feasibility of using Access as a backend, a data repository using the default database engine, Jet. The subject of using Access as a frontend, or User Interface, will be discussed in a later article.


Record Count -- a Non-Issue

Usually the first thing that people think about when gauging whether Access is the right tool for them is the approximate number of records they believe their database will eventually contain. They believe that having large numbers of records rules out using Access. This is false.

Access is not limited by record count, either in a single table or cumulatively across tables.

There have been many documented cases of a single Access database containing millions of records. So put your mind at ease, you will never have too many records.


File Size

Since Access is a file-based database, there is a maximum file size that is imposed, largely in part due to the limitations of the operating system.

The maximum size for an Access database version 2000 and later is 2 GB.

The maximum size for an Access database version 97 and earlier is 1 GB.

So while record count is not a limitation in and of itself, if you have enough records that your file size grows closer to the 2 GB limitation, you'll now have a decision to make as to how to deal with the situation, because you will not be able to add additional records beyond that point.

Possible resolutions include:

  • Splitting the data into more than one backend database. This carries the downside of not being able to enforce referential integrity across the two databases.
  • Upsizing the database to a more robust solution, like SQL Server, MySQL, and the like. This solution may cause you to have to learn a new flavor of SQL and change how you interact with the data.


Concurrency

One of Access' great strengths is that multiple people can use the same data warehouse simultaneously.

According to the specifications of Access's Jet engine, it is able to handle 255 concurrent connections. While this is true, sadly in practical usage, the number of concurrent users is closer to 20-25 users. Once you start adding more users than that, performance falls sharply, anywhere from sluggish responsiveness to being unstable and unusable.

This bottleneck has a sliding scale, though. A skilled programmer can increase this number significantly by using advanced and efficient tactics to get at the data. Using unbound forms, only grabbing the required records for the task at hand, and the "Get In, and Get Out Quickly" philosophy goes a long way in improving an application's performance.

The opposite is also true. Poor architecture of the data and sloppy code can bring performance to a crawl for even just a few users.


Scalability

Access is a LAN solution. This means that if you do not have a wired, always-on connection to the backend data, Access will not perform well, or at all.

Wireless networks are not stable enough to support Access interaction. Using them may cause very poor performance, as well as puts the data at risk of corruption. It's just a bad idea.

Connecting to an Access database across the Internet via a VPN is a poor choice, as well. They are simply not stable enough.

So, what's a user to do if he has remote users who do not have direct access via a LAN to the data?

Terminal Services

Utilizing a terminal server along with a VPN is a very good method to connect across the internet. Thin-client technology (like Citrix or Microsoft's Remote Desktop Services previously known as Terminal Services) is probably the most preferred method to achieve this.

Replication

Replication is essentially taking a copy of a database offline, making changes to that local copy, and then synchronizing the data to the master copy at a later time. This process can become very complicated if there are many conflicts to resolve.

Replication is an option for Access versions up to 2003. Partly deprecated in 2007 and fully deprecated in 2010, native replication is no longer a viable option in these versions.

Some users have created home-grown replication, that is, managing the sync process in a non-native way. This is not an easy undertaking.

SharePoint

In recent years, most notably with Access 2007 and later, Microsoft has put a massive amount of effort into using SharePoint in combination with Access to provide web availability to Access in a native way.

While this is still in the fine-tuning stages, the future may support scaling out your Access databases using SharePoint as the natural evolution to making your databases web-enabled.


Security

Security of data is always a concern. The bottom line is this: There is no way to completely secure an Access database 100%. If your data is so sensitive that you cannot bear a breach, then using Access to store your data is not the solution for you.

Being a file-based database, Access inherently carries the limitation of being "hack-able". That does not mean that it isn't a good solution. But it can keep honest users honest, and is perfectly acceptable for many, many business needs.


Conclusion

Access, as a database, is a very powerful tool in the hands of a skilled developer. Many full-featured applications have been built with it over the years. You may even use some yourself without even knowing it. You can have a rock solid application built entirely in Access provided you adhere to the above mentioned limitations.

Hopefully this article will allow you to step through the looking-glass with eyes wide open, knowing whether it is the right choice for you.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 9,769 times.  This page was last modified 11:29, 14 February 2012 by Jack Leach. Contributions by Cpetermann, Ydecavoqex, Walter Niesz and Glenn Lloyd  Disclaimers