Which back-end database option should I use - Microsoft Access® or SQL Server®?

Catalyst Manufacturing® can be used with either a Microsoft Access® or SQL Server® back-end database. A Microsoft Access® database is typically adequate for companies with less than 10 concurrent users and has advantages in terms of cost and ease of use. As the number of users and data storage requirements increase, however, SQL Server® provides several advantages including the ability to:

  •  Store more data
    •  in the range of terabytes instead of the 2 gigabyte limit for Access.
  •  Support more concurrent users
    • Microsoft Access theoretically supports up to 255 users, but performance degradation tends to impose a practical limit of 10-15 simultaneous users. In addition, an Access database can become damaged if either the client or server computer (or the network connection) fails during a transaction.
  •  Provide dynamic backups and automatic recoveries in case of failure
    • SQL Server logs transactions so that updates made within a transaction can always be recovered or rolled back to the last consistent state if either the client or the server computer fails. The transactions in Access database files are not managed by a separate transaction log and can fail without recovery if the database file becomes damaged.[1]
  •  Enhance user-level security
    • SQL Server supports Windows NT® security accounts to authenticate users that log on to a database. Unlike security for Access databases, if users have already logged on to a Windows NT network, the existing Windows NT security groups and accounts can be used to define permissions in the back-end database.[1]

[1] Roberts, Mark; Chapter 1: Understanding Microsoft Access 2000 Client/Server Development; Mark Roberts, Microsoft Corporation; February 2000

Have more questions? Submit a request


Please sign in to leave a comment.
Powered by Zendesk