System Architecture

Chapters 1 and 2 described how Access has grown from a personal database management system (DBMS), to one that is nearly adequate for an enterprise, but here, I want to get more specific about what “personal” versus “enterprise” means. In a personal DBMS, the database, VBA code, and user interfaces reside on a single computer and are normally used by one person at a time. This concept gets expanded a bit when speaking about a DBMS in a workgroup environment. In this case, one computer serves as the host to hold the DBMS; however, that DBMS can be accessed by multiple people on different workstations. Access can very easily serve both these roles.

Enterprise environments, such as SQL Server, Oracle, and DB2, distribute data to a vast number of users quickly and efficiently. As an example, if you were building a website, you would need an enterprise system to handle the thousands of requests it could receive each day. While Access has gotten very powerful, it still cannot handle that sort of demand.

In many cases, Access runs on a computer called a file server. Early file servers were slow and unreliable and often did not have the capability of knowing what information to send and not to send. So, for example, if you just needed a couple of records from the database, it would send you the whole database file. Fortunately, this inefficient system evolved into a more powerful client-server model in which the database system can accept a request for records, process that request, and send only the required information back.

With the growth of the Internet, we’ve seen the growth of web servers in which you can type a request in a web form that has been downloaded to your web browser. The request is sent to, say, Amazon.com’s web server. Their web server reads the request and hands it over to the database server, such as SQL Server. The database application sends it back to the web server that then sends it to a dynamic application server (such as ASP, ColdFusion, ASP.NET, JSP, or PHP), which then converts it to HTML code. The HTML packet is then sent back to the web server, which puts a return address on it and sends it back to your web browser. Your browser reads the HTML and displays the results on your screen. And all of this happens in a matter of seconds.

If you are in a low-demand situation, Access can handle many of these functions. But if you have high-volume demands, you will need to move up to an enterprise system. In some cases, you can take advantage of the easy-to-use tools in Access to build a nice front end to the enterprise-level SQL Server. That way, you have the best of all worlds.