Partitioning Databases

A single relational database can contain a set of tables that represent every tangible object with which an organization deals. It's more common, however, to create individual databases that relate to specific organizational functions typically sales, manufacturing, finance, and human resources. Each database is owned and managed by its department, and other departments only have access to specific fields of selected tables. For example, the sales department's order entry system must associate salespersons with their orders, but doesn't need access to employees' personnel information maintained by the human resources department.

As mentioned earlier, Jet databases let you link table data stored in other Jet, desktop, and client/server databases. Linked tables can reside on any computer to which you have a network connection and security permissions to read and, optionally, update the data. In the case of an employees table, you might find it necessary to split the table to prevent linked access to confidential employee information. Splitting a base table for linking is one of the few instances in which you use a one-to-one relationship.

SQL Server 2000 lets you link multiple servers, which provides access to every database stored on the linked server, including table and view objects, for which you have appropriate SQL Server permissions. You can write T-SQL distributed queries to return data from more than one database. SQL Server's Distributed Transaction Coordinator (DTC) enables transactions to include tables in more than one database. You also can link Windows 2000+ Server's Active Directory (AD) to an SQL Server database by using the OLE DB Provider for Microsoft Directory Services. The capability to integrate data about users, security groups, organizational units, domains, and other AD objects into Access applications is a useful feature for medium- to large-size organizations.

For brief introduction to linking Jet and SQL Server databases, see "Linking Remote Servers," p. 842.

Another method of distributing information between multiple databases is called replication. Replication periodically updates a copy of selected data in another database; updates occur only for changes that have occurred since the last replication. Replication is an ideal method for providing mobile users with tables containing the data they need while disconnected from the network. Partial database replicas let you replicate a particular set of columns or rows of a table, which eliminates the need for multiple tables to restrict distribution of confidential information. Conventional Access replication only supports Jet databases but lets you replicate application design changes in addition to data.

SQL Server replication uses the publish-subscribe model and works only with data. SQL Server 2000 can replicate data to and from Jet 4.0 databases by using merge replication. Database administrators (DBAs) often use one-way snapshot replication to provide department-level Access developers with subscriptions to the information they require from corporate databases. The advantage of snapshot replication is that users of the departmental database don't need permission to connect to the corporate database. ADP use SQL Server replication and don't permit using Jet replication to update application objects.

Note

Access replication for Jet databases and SQL Server replication are advanced database topics and are beyond the scope of this book. You should, however, be aware of replication options when you make the decision between creating a conventional Jet application or SQL Server-based ADP.