Chapter 36: Client/Server Concepts
1189
Microsoft intends for Access to be used primarily as a single-user or workgroup database system,
and not as a database engine driving large Web sites or supporting applications used by hundreds
or thousands of simultaneous users. Access processes its data locally, on the user’s computer.
When an Access database is split, and the back-end .accdb resides on a file server, Access pulls
data from the back-end database, and processes it on the user’s computer. The file-based database
model is not suitable for many environments outside of personal and workgroup computing.In contrast, server database engines like SQL Server and Oracle, process data on the server com-
puter, and deliver requested data to the client application. Neither SQL Server nor Oracle provides
any kind of user interface; they both rely entirely on other software to support end-user applica-
tion requirements. The client-side application is responsible for supporting the user interface
and responding to user input. This division of operations is the primary difference between a
file-oriented database system like Access and a server database engine like SQL Server.Access’s role in client/server computing
Access fulfills a rather unique dual role of database administration and application development.
As you’ll see in Chapters 37 and 38, Access is well suited as both an administrative tool for work-
ing with SQL Server databases, and an application development tool. With few exceptions, devel-
oping Access applications that use SQL Server data is identical to building the same applications
using data stored in Access .accdb files.The Access Database Engine doesn’t provide the multiuser/multitasking capabilities of SQL Server.
For example, in addition to relying on stored procedures, databases like SQL Server and Oracle
also support highly specialized database objects, such as the following:l (^) View: A stored query definition containing no data. A view doesn’t contain data, but it
delivers data when the view is requested by a client application. In many ways, a SQL
Server view is analogous to an Access Select query. An Access application treats a SQL
Server view as if it were a table, even though the data is dynamically selected from the
underlying tables as the view is requested.
l Clustered index: A special type of index that maintains the physical order of records in a
table to match the table’s primary index.
l Identity fields: Maintains sequential index counters. Typically used to generate surrogate
primary keys for creation of new records in a table in a relational database. Identity fields
are very similar to Access AutoNumber fields.
Note
A surrogate key is where an integer identifier is used to replace a primary key in a table.
l (^) Temporary table: Used to temporarily store data, usually for intermediary steps in larger
operations. SQL Server temporary tables exist only while a stored procedure is running;
they’re used to temporarily hold data used by the stored procedure. Temporary tables may
be used by more than one SQL Server stored procedure at a time.