The major problem facing multiple-office programs is their need to share information on a timely basis. We don’t believe that there is just one answer to this challenge, rather there are multiple ones. Which one is right for your program is dependant on your resources, your experience level, your location and your need for real time information.

There are numerous options for transferring information between offices in the Access environment. No matter what the options are, they are subject to certain physical limitations caused by the media over which the information must travel. I like to describe the capabilities of this media as a water pipe and the information as the water. This analogy is useful to understand a very complex process.

There is a pipe inside of every personal computer which transfers information from the hard drive to the CPU or Central Processing Unit (now, usually a Pentium processor). This pipe can be thought of as a 100 foot diameter water pipe. If you put this computer on a LAN, or local area network, a new pipe is introduced into the equation, the network wire. Depending on the speed of the network (10 Megabit or 100 Megabit), this pipe might be 5 feet or 50 feet in diameter. So far our computer system is working with high speed connections.

When a desktop database engine such as Microsoft Access Jet engine requests information, such as the Jones file, over the network, the file server does not send the Jones file. The workstation jet engine figures out what pages of data it needs from the server database by reading the indexed fields from the database on the server.  Next, it locks and grabs those pages of data, then pulls them across the network.  When the work is done, the pages are pushed back across the network.  Where the pipe is big, this isn’t a problem.

Attempting to move this same information through a normal modem is like attempting to move the 5 foot wall of water through a soda straw. For some operations this is not a problem, since there is little information being transferred or it only needs to be transferred once. Examples might be a transfer of an Email message or the movement of a word processing file. However, where there is an attempt to move a lot of information repeatedly, there is a problem. An example of this situation is where the user is accessing a large database to do repeated conflict checks, reports and data entry during the day. This sort of need is usually met by use of what is referred to as a WAN or Wide Area Network.

If we think of our analogy of moving water, there are four alternatives to deal with moving water between the main office and branch offices:

1. Let the water run longer. This is usually unacceptable when the time increases by one hundred-fold or more.

2. Don’t give a real time connection to the branch office.

3. Increase the diameter of the pipe.

4. Reduce the amount of water that needs to be sent.

Don’t give real time connection

This is a common approach to the data transfer dilemma. Data is updated into the central office on a regular basis by using floppy disks, tapes, or transfer via modem. This transfer could occur as often as daily or as seldom as a yearly.

Microsoft Access 97 has some very sophisticated techniques for doing this via a LAN, WAN, Remote Access or even the Internet. Its technique is referred to as data replication, and it can be done with the complete or limited parts of the database. The usual example given is salespeople hook up to a LAN with their laptops in the morning and get an update as to sales figures and information about only their sales contacts. They then take the laptops into the field, enter orders, look up information, and reference parts catalogs. At the end of the day, they hook up their computers to the LAN and upload the information to the main database.

In this model, there is one main database and a series of replicas that are periodically updated. This updating is done automatically on a scheduled basis or can be done with a drag and drop technique.

Increase the diameter of the pipe

Increasing the diameter of the pipe is technically referred to as increasing the bandwidth. In telephone terms, this means providing a dedicated line and increasing the capacity of that line. Examples of increased capacity telephone lines are ISDN and T1.

The way that this transfer works requires you to understand that when information is put on a LAN it is placed in multiple shoe boxes (packets), each having the address of the computer authorized to get the information out of the shoe box. The box then travels to every computer, but only the computer with the proper address can open up the box and take out the information. If a WAN is installed, a special device called a router is placed on the network wire. This device reads the boxes and notes any addresses that are not on the Local Area Network. Where it finds such a box, it puts it through a high speed modem and sends the information to the appropriate network in another location. A router there receives the box and puts it on the network wire so the appropriate computer can pick up the information. The computer on the remote network can respond with the requested information and the router on its network sends the new box with the information back.

The problem with this situation is that T1 lines are expensive, as is the expense and complexity of setting up routers, etc.

Reduce the amount of water sent

Another solution is to reduce the amount of information that needs to be sent. The cheapest way to do this is to use remote access. This is a process using modems and software to transfer information. An example of this software is PC-Anywhere. A person in the branch office dials into the central office and enters information into the computer or searches the database through their modem. How does this software get around the problem of transferring information through the narrow pipe of the modem?

It does this by not transferring the information at all. The information is actually processed and worked on by the computer sitting in the central office. The only thing that is sent to the branch computer is screen updates. The only things sent by the branch computer are keystrokes and mouse clicks. Most of these software packages also allow the branch computer to print remotely. A normal phone line and modem can be used.

The problem with this situation is that only one person can dial into the central office per phone line and only one person may use the PC Anywhere machine at a time. Many times only a single connection can be used by a single branch office, otherwise multiple phone lines and workstations are needed. It would be prohibitive to having a branch office with five people putting in their time records at one time.

A second way around the problem is to reduce the amount of information transferred by using a client/server based database management system, rather than the normal LAN file server. An example of this is Microsoft SQL (pronounced see-quel) Server. SQL in a traditional sense is an abbreviation for Structured Query Language, which was created by IBM in the mid seventies as a standardized query language for requesting information from a database.  Microsoft however has created a software product that acts as a database management system/server and named it SQL Server. When a workstation needs information it sends a request to the SQL server, the server then processes the request and sends the information to the workstation. An analogy might be in order:

Imagine you were buying a car and wanted to find out whether a car dealer had any blue cars. With a file server situation, you’d call the dealer up, he’d say wait. He and his employees would then drive all the cars in the lot to your house where you could count the blue cars. With a database server, he’d look out the window, count the blue cars and tell you the number.

Microsoft SQL Server has some other advantages. It runs under Windows NT and is highly scalable. What this means is that you can increase the memory, processors and hard drives on the server and the program will realize a speed increase. Windows NT will support up to 16 processors and advanced hard drive systems, such as RAID.

Even though it is a different database "engine" from Access, there is another advantage. Access can work as a "front end" for the SQL "back-end". This means that the user sees the same Clients for Windows interface and isn’t even aware that the processing is occurring mostly on the server rather than on the local work station.  When combined with the right WAN this should provide excellent performance.  What is the right WAN?  It depends on many things, including the number of users you have and if you will be using the WAN for any other communications.

One disadvantage of this system is that if the WAN or server goes down, all offices are without database access.

Internet entry of information or Frame Transfer are two similar techniques that also allow the work to be done on the remote machine. They too are subject to the size of the pipe and attempt to do the work on the remote computer. SQL can be combined with Internet access to provide remote access to a database. Access 97 also supports direct publishing to the Internet and Active Server Pages, basically a remote database access technique.

Copyright © 2002 All rights reserved Kemp's Case Works, Inc.