Chapter 17. Understanding Client/Server Development with VBA

Access makes it very easy to create applications that interact with other desktop database formats and enterprise-level database servers. However, the easiest methods are not always the best, and wrong choices can have serious unintended effects in the long run. A thorough understanding of how Access interacts with other databases and the various alternatives available for developers is critically necessary to make the best design choices for a specific application.

In a typical business environment, Access MDB applications tend to sprout up from nowhere because some individual or small group wanted functionality that the IT department just wasn't providing. After the file is created, other people notice the application's usefulness and ask to use it themselves. Before long, the application is shared on a file server and becomes an unintended but nonetheless critical piece of company's business processes. Perhaps at some point, someone had even split the data tables into a file of its own and added links from the existing application so that users could store a front-end application on their local machines and connect to the tables stored on a central server.

However, merely storing your data on a server does not make your Access file a client/server application. At minimum, client/server development implies that the processing is separated between at least two processes, one on the client and one on the server. This is an architectural difference in the structure of an application. A traditional Access MDB application with no linked ODBC (Open Database Connectivity) tables is a file-server application, where all processing occurs on the local client machine regardless of where the data files are physically stored. Just as storing a MicrosoftWord document on a server share does not make Word a client/server application, neither does storing MDB files on a server make Access client/server.

Although it's tempting to think that moving your data tables to a server database such as Microsoft SQL Server will make your application "client/server," in reality this is only the beginning. It's more accurate to say that moving your data to SQL Server gives you the potential for a client/server application. Your application needs to take advantage of that potential for any benefits to be realized. For reasons that will be explained later in this chapter, it is not uncommon for an application to be slower immediately after migrating data from an MDB file to SQL Server (or a similar server database) and relinking the tables in your front-end MDB application. Regardless of the client format you choose, significant performance and other benefits can be realized by utilizing good design practices.