Microsoft Access VBA Programming for the Absolute Beginner

When you access data with Access, you actually access the tables directly. First of all, this could create some serious security issues. Secondly, it also severely limits how many people can access the data simultaneously and how efficiently the requests can be handled.

On the other hand, SQL Server handles all requests through, as the name implies, a server. This means both better security and better efficiency. This will become obvious as we progress through this chapter.

Let’s start by listing some of the advantages that SQL Server offers:

• Improved security because of the strong use of groups and roles.

• Better client handling (the ability to handle a greater number of visitors more quickly and securely) because of server access, as opposed to file access. SQL Server can handle hundreds of users at the same time.

• Larger database sizes. Access has a limitation of 2GB, while SQL Server can handle terabytes.

• Users have no access to the database file, while Access users access the data directly from the database file.

These improved features come at a price:

• Licensing costs are not inexpensive. Depending on circumstances, licensing could run to $10,000 +.

• Depending on how much traffic SQL Server will handle, you may need to purchase dedicated hardware to handle the resources.

• You will need to learn many additional skills, such as writing stored procedures, server management, and using T-SQL.