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