There are many methods for handling concurrent access to data and other application objects by multiple users; each one offers both solutions and limitations. It’s important to select the best solution for your particular environment.

Just as there are different strategies for installing Access, there are also various strategies for installing your application, such as the following:

In other words, after you have created an application, you can place the entire application on the network, which means that all the tables, queries, forms, reports, macros, and modules that make up the system reside on the file server. Although this method of shared access keeps everything in the same place, you will see many advantages to placing only the database’s data tables on the file server. The remaining objects are placed in a database on each user’s machine, and each local application database is linked to the tables on the network. In this way, users share data but not the rest of the application objects.

The advantages of doing this are as follows:

In addition to storing the queries, forms, reports, macros, and modules that make up the application in a local database, I also recommend that you store the following objects in each local database:

Temporary tables should be stored in the database that’s on each workstation because, if two users are performing operations that build the same temporary tables, you don’t want one user’s process to interfere with the other user’s process. The potential conflict of one user’s temporary tables overwriting the other’s can be eliminated by storing all temporary tables in each user’s local copy of the database.

You should also place static lookup tables, such as state tables, on each workstation. Because the data doesn’t change, maintenance isn’t an issue. The benefit is that Access doesn’t need to pull that data over the network each time it’s needed.

Semistatic tables—tables that are rarely updated—can also be placed on the local machine. As with static tables, having these tables in a local database means reduced network traffic and better performance, not only for the user needing the data, but also for anyone sharing the same network wire. Changes made to the semistatic tables can be transported to each workstation by using replication (covered briefly in this chapter and in significant detail in Alison Balter’s Mastering Access 2002 Enterprise Development).

The configuration described throughout this section is illustrated in Figure 20.1.

Figure 20.1. An example of a configuration with database objects split, storing temporary and static tables locally and shared tables remotely (on the file server).

The final option has recently emerged as a viable alternative for deployment of an Access application. It addresses both bandwidth and centralization issues. With this option, a Windows 2000 machine runs the Windows 2000 Terminal Services. Client machines then access the server machine using the Terminal Server Client Utility. In this scenario, Access, your application, and the data that it accesses, are all installed on the Windows 2000 Server machine. All other machines access the application via user sessions created on the server machine. Keystrokes and mouse events are sent from the client machines to the server machine. The resulting screen image is returned to the client machine. This configuration addresses many of the problems inherent in the two other solutions.

Linking to external data, including data not stored in another Access database, is covered extensively in Chapter 19, “Using External Data.” Three options are available to you:

The first two options are covered in Chapter 19. The last option, the Database Splitter Wizard, is covered here. To split the objects in a database into two separate .MDB files, follow these steps:

As the volume of data in your Access database increases, you probably will notice a degradation in performance. Many people say that 100MB is the magical number for the maximum size of an Access database, but many back-end database servers can handle databases containing multiple gigabytes of data. Although a maximum size of 100MB for an Access database is a good general guideline, it is not a hard-and-fast rule. You might find that the need to upsize occurs when your database is significantly larger or smaller than 100MB. The magic number for you depends on all the factors discussed in the following sections, as well as on how many tables are included in the database. Generally, Access performs better with large volumes of data stored in a single table rather than in multiple tables.

Just as a large volume of data can be a problem, so can a large number of concurrent users. In fact, more than 10 users concurrently accessing an Access database can degrade performance. As with the amount of data, this is not a magical number. I have seen applications with fewer than 10 users where performance is awful, and I have seen applications with significantly more than 10 users where performance is acceptable. It often depends on how the application is designed, as well as what tasks the users are performing.

Certain applications demand better performance than other applications. An Online Transaction Processing system (OLTP) generally requires significantly better performance than a Decision Support System (DSS), for example. Suppose that 100 users are simultaneously taking phone orders. It would not be appropriate for the users of the system to ask their customers to wait 15 seconds between entering each item that is ordered. On the other hand, asking users to wait 60 seconds to process a management report that users run once each month is not a lot to ask (although many still will complain about the minute).

Most back-end database servers can use multithreaded operating systems with multiple processors to handle large volumes of user demand; Access cannot.

As a file server in an organization experiences increasing demands, the Access application simply might exacerbate an already growing problem. By moving the application data to a database server, the overall reduced demands on the network might provide all users on the network with better performance regardless of whether they are using the Access application.

Probably one of the most exaggerated situations I have seen is one in which all the workstations were diskless. Windows and all application software were installed on a file server. All the users were concurrently loading Microsoft Word, Microsoft Excel, and Microsoft PowerPoint over the network. In addition, they had large Access applications with many database objects and large volumes of data. All this was stored on the file server as well. Needless to say, performance was abysmal. You can’t expect an already overloaded file server to handle sending large volumes of data over a small bandwidth. The benefits offered by client/server technology can help alleviate this problem.

The backup and recovery options offered with an Access MDB database stored on a file server simply do not rival the options for backup and recovery on a database server. Any database server worth its salt sports very powerful uninterruptible power sources (UPSs). Many have hot-swappable disk drives with disk mirroring, disk duplexing, or disk striping with parity (RAID Level 5). With disk mirroring and duplexing, data can be written to multiple drives at one time, providing instantaneous backups. Furthermore, some database server tape backup software enables backups to be completed while users are accessing the system. Many offer automatic transaction logging. All these options mean less chance of data loss or downtime. With certain applications, this type of backup and recovery is overkill. With other applications, it is imperative. Although some of what back ends have to offer in backup and recovery can be mimicked by using code and replication, it is nearly impossible to get the same level of protection from an Access database stored on a file server that you can get from a database stored on a database server.

Access offers what can be considered the best security for a desktop database. However, it cannot compare with the security provided by most database servers. Database server security often works in conjunction with the network operating system. This is the case, for example, with Microsoft SQL Server and Windows NT Server. The user is given no direct rights to the physical database file; it can be accessed only via an ODBC data source or an ADO connection. Remember that no matter how much security you place on an Access database, this does not prevent a user from seeing or even deleting the entire MDB file from the network disk.

It is very easy to offer protection from this potential problem, and others, on a database server. Furthermore, many back-end application database server products offer field-level security not offered within an Access MDB file. Finally, many back ends offer integrated security with one logon for both the network and the database.

The Access MDB file format is proprietary. Very few other products can read data stored in the Access database format. With a back-end database server that supports open database connectivity (ODBC), front-end applications can be written in a variety of front-end application software, all concurrently using the same back-end data.

You must evaluate the specific environment in which your application will run:

After you answer these and other questions, you can begin to decide whether the benefits of the client/server architecture outweigh the costs involved.

The good news is that it is not an all-or-none decision. Various options are available for client/server applications using Access as a front end. Furthermore, if you design your application with upsizing in mind, moving to client/server technology will not require you to throw out what you have done and start again. In fact, Microsoft provides an upsizing wizard that makes upsizing to a SQL Server database a relatively painless process. How painless depends on numerous factors, including how complex your queries are, whether your queries include VBA functions, and other factors that are covered later in this chapter, and in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

Earlier in this book, you learned about using Access as both the front end and the back end. The Access database is not acting as a true back end because it is not doing any processing. Figure 20.7 shows the architecture in this scenario. The Access application resides on the workstation. Access uses the Microsoft Jet Engine to communicate with data stored in an Access MDB database file stored on the file server.

Figure 20.7. Access as a front end using an MDB file for data storage.

In the second scenario, back-end tables can be linked to the front-end application database (.MDB). The process of linking to back-end tables is almost identical to that of linking to tables in other Access databases or to external tables stored in FoxPro, Paradox, or dBASE. And, the linked tables can be treated like any other linked tables. Access uses ODBC to communicate with the back-end tables. (See Figure 20.8.) Your application sends an Access SQL statement to the Access Jet Engine, which translates the statement into ODBC SQL. This ODBC SQL statement is then sent to the ODBC Manager, which locates the correct ODBC driver and passes it the ODBC SQL statement. Supplied by the back-end vendor, the driver translates the statement into the back end’s specific dialect. The now back end–specific query is sent to the SQL server and to the appropriate database. As you might imagine, all this translation takes quite a bit of time. Furthermore, ODBC is becoming a technology of the past; it is quickly being replaced by the ADO/OLEDB technology. That is why one of the two alternatives that follow might be a better solution.

Figure 20.8. Access as a front end using links to back-end tables.

One of the bottlenecks of using linked tables is waiting for all the translation to happen. Because of this and for the following reasons, you want to bypass the translation process:

As an alternative, you can execute a pass-through query written in the syntax specific to the back-end database server. Although the query does pass through the Jet Engine, Jet does not perform any translation on the query. Neither does ODBC. The ODBC Manager sends the query to the ODBC driver, which passes the query to the back end without performing any translation. In other words, exactly what was sent from Access is what is received by the SQL database. Figure 20.9 illustrates this scenario. Notice that the Jet Engine, the ODBC Manager, and the ODBC driver are not eliminated entirely. They are still there, but they have much less impact on the process than they do with attached tables. Pass-through queries are covered in more detail in the “Using Pass-Through Queries” section of this chapter.

Figure 20.9. Access sending a pass-through query to a back-end database.

As you will see later in this chapter, pass-through queries are not a panacea, although they are very useful. The results of a pass-through query are not updateable, for example. Furthermore, because pass-through queries are written in the back end’s specific SQL dialect, you must rewrite them if you swap out your back end. For these reasons and others, pass-through queries generally are used with other solutions.

A stored procedure is compiled SQL code stored on a back end. It is generally executed using ADO or DAO code. You can also execute a stored procedure using a pass-through query. Regardless of what you call it, the code within the stored procedure is written in the SQL native to the back end on which it is stored, and the stored procedure is executed in entirety on the back end. Stored procedures can return results or can simply execute on the back end without returning any data.

An additional, very viable solution is available when working with a back-end database server. This involves using a Microsoft Access Data Project (.adp), which was introduced with Access 2000. By using a Microsoft Access Data Project (.adp), you bypass the Jet Engine entirely. An Access project contains only code-based objects such as forms, reports, data access pages, macros, and modules. All tables, views, database diagrams, functions, and stored procedures are stored in a SQL Server database. After you have connected with a SQL Server database, you can easily view, create, modify, and delete SQL Server objects. Figure 20.10 illustrates this scenario. Notice that neither the Jet Engine nor ODBC is involved in the scenario.

Figure 20.10. Access using a Microsoft Access Data Project to communicate to a back end.

When exporting a table to a server, no indexes are created. All indexes need to be re-created on the back-end database server. If your database server is running Microsoft SQL Server, you can use the Access Upsizing Wizard for Access 2000 or Access 2002. These wizards create indexes for server tables in the place where the indexes exist in your Access tables.

AutoNumber fields are exported as Long integers. Because some database servers do not support autonumbering, you have to create an insert trigger on the server that provides the next key value. You also can achieve autonumbering by using form-level events, but this is not desirable. The numbering will not be enforced if other applications access the data. If you are upsizing to Microsoft SQL Server, the Upsizing Wizard for Access 2000 and Access 2002 convert all AutoNumber fields to Identity fields.

Default values are not automatically moved to the server, even if the server supports them. You can set up default values directly on the server, but these values do not automatically appear when new records are added to the table unless the record is saved without data being added to the field containing the default value. As with autonumbering, default values can be implemented at the form level, with the same drawbacks. If the Upsizing Wizard for Access 2000 or Access 2002 is used to move the data to Microsoft SQL Server, default values are exported to your server database.

Validation rules are not exported to the server. They must be re-created using triggers on the server. No Access-defined error messages are displayed when a server validation rule is violated. Your application should be coded to provide the appropriate error messages. You also can perform validation rules at the form level, but they are not enforced if the data is accessed by other means. If the Upsizing Wizard for Access 2000 or Access 2002 is used to move the data to Microsoft SQL Server, validation rules are exported to the server database.

Relationships need to be enforced using server-based triggers. Access’s default error messages do not appear when referential integrity is violated. You need to respond to, and code for, these error messages in your application. You can enforce relationships at the form level, but as with other form-level validations, this method of validation does not adequately protect your data. If the Upsizing Wizard for Access 2000 or Access 2002 is used to move the data to Microsoft SQL Server, all relationships and referential integrity that you have set up in your Access database are set up within the server database.

Security features that you have set up in Access do not carry forward to the server. You need to re-establish table security on the server. After security is set up on the server, Access is unaware that the security exists until the Access application attempts to violate the server’s security. Then, error codes are returned to the application. You must handle these errors by using code and displaying the appropriate error message to users.

Servers often have much more stringent rules than Access does regarding the naming of fields. When you export a table, all characters that are not alphanumeric are converted to underscores. Most back ends do not allow spaces in field names. Furthermore, most back ends limit the length of object names to 30 characters or fewer. If you already have created queries, forms, reports, macros, and modules that use spaces and very long field and table names, these database objects might become unusable when you move your tables to a back-end database server.

Most back ends have many reserved words. It is important to be aware of the reserved words of your specific back end. It is quite shocking when you upsize a table and find that field names you have been using are reserved words on your database server. If this is the case, you need to rename all the fields in which a conflict occurs. Once again, this means modifying all the queries, forms, reports, macros, and modules that reference the original field names.

Many back-end databases are case sensitive. If this is the case with your back end, you might find that your queries and application code don’t process as expected. Queries or code that refer to the field or table name by using the wrong case are not recognized by the back-end database and do not process correctly.

Most properties cannot be modified on remote tables. Any properties that can be modified are lost upon export, so you need to set them up again when the table is exported.

Certain properties and methods that work on Access tables might not work on remote tables. This might necessitate some coding changes after you export your tables.

In today’s global economy, it’s the norm for companies to have many offices distributed throughout the country, or even the world. Before Access 95, it was difficult to implement an Access application that would support sharing data among several offices. However, with replication, each office can have a replica of the database. Periodically throughout the day, each office can synchronize its changes into data at corporate headquarters. How often the synchronization happens depends on the frequency required for data at each location to be current at any given moment.

Sharing data among dispersed users is illustrated by the salespersons example used earlier. This implementation of replication generally involves mobile users who connect to the network after modifying data out on the road. Because only incremental changes are transferred from the design master (the original) to the replicas (the copies), and from the replicas to the design master, this form of replication makes the mobile computing scenario economically feasible.

Replication can be very effective in reducing network traffic loads. The design master can be replicated onto one or more additional servers. Distributed users can then make changes to one of the additional servers, which significantly improves performance by distributing the processing load throughout the network. Changes made to the data on the additional servers can be synchronized with the main server periodically during the day.

Replication is an excellent vehicle for distributing application updates. Design changes can be made only to the design master; therefore, as users throughout the organization log on to synchronize with the design master, any structural changes to the application are sent to the user. This is much more efficient and effective than giving every user an entirely new copy of the application database each time a minor change is made to the application’s schema.

Many people don’t think of replication as a means of backing up application data, but replication is extremely well suited for this task. Ordinarily, to back up an Access database, everyone must log off the system, but that’s not necessary with replication. The synchronization process can occur periodically during the day while users are still logged on to the system, and all changes are replicated. Not only is this more efficient than backing up the entire database, it also ensures that you can quickly be up and running on a backup server if there’s a problem on a main server.

The Access user interface gives you a series of menu items that allow you to perform all the steps of the replication process. The Tools|Replication menu has the following options: Create Replica, Synchronize Now, Partial Replica Wizard, Resolve Conflict, and Recover Design Master. These menu options are covered throughout this chapter.

The Windows Briefcase supplies the foundation Access needs for the replication process. Users can simply drag a database file to the Briefcase to replicate it, make changes to the file while on the road, and synchronize the replica with the design master when they reconnect to the network. This is done because, when Access is installed, it registers a special class ID with the Windows Briefcase. When a database is dragged to the Briefcase, the Briefcase’s Reconciler code is called. When the user selects Update Selection or Update All from the Briefcase menu, the Merge Reconciler is called. Briefcase replication is available as an installation option through Windows 95, its successors, and Windows NT 4.0 and later.

The Replication Manager is a sophisticated tool that’s part of Microsoft Office 2000 and Office 2002 Developer. It’s a mandatory player in the replication process when you’re managing many replicas. Besides providing basic replication functionality, the Replication Manager lets you schedule the synchronization of replicas. In fact, the Replication Manager allows you to manage and intricately control all aspects of the replication process. The Replication Manager is covered in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

Most aspects of the replication process can also be done by using ADO code, which can be used to make a database replicable, create and synchronize replicas, and get and set properties of a replicable database. ADO can easily be integrated with the other methods of replication. Although it requires the most time and effort on your part, ADO code lets you base replication on events rather than time and give your users a custom user interface for the replication process.

Visual Basic 4.0 and later, Excel for Windows 95 and later, and Visual C++ all support replication using Data Access. You can’t perform replication with these products by using either the Briefcase or Microsoft Office Developer, however, so it’s easier to manage the replication process on a machine that has Access installed.