| Not so long ago, a company came to me with a problem. The company needed a better system to enable its employees to reserve rooms for meetings, conferences, and other functions. The company had about 2,200 employees in four buildings, and it had barely enough meeting rooms, so a room reservation system that operated accurately and smoothly was essential. For some time, if you wanted to reserve a room, you phoned administration and told them when your meeting was scheduled and where you wanted to hold it. An administrative assistant checked a wall calendar to see whether the room was available on your hoped for date and time, and if it was, you gave the rest of the particulars what refreshments should be served, whether A/V equipment was needed, what account should be charged, and so on. Once a week, that same assistant would gather the information together and re-enter it into a two-page form, showing each day for the following week and listing each scheduled meeting on each day. Each meeting also showed the assigned room and the scheduled start time. A typical day had about 20 meetings. The form was duplicated and posted in various locations throughout the building each Friday evening. There were problems with this system. The paper-and-pencil approach to gathering, storing, and retrieving information about meetings caused the standard problems of incomplete and illegible information, overbooking of rooms, lost reservations, and so on to say nothing of one entire person-day each week devoted to compiling the information and typing the form. So, when a campuswide data network was installed, the company thought it was time to bring its room reservation system up to date. By putting it on the network, it would be easier for staff to check the status of rooms that they had reserved, and possibly decentralize the entry of reservations. They purchased a shrink-wrap room scheduler for $6,000. One of their more technically savvy staff, who understood both their existing problems and the nature of the new software, installed it. But the company had not taken into account that its employees were used to the forms that for years had been posted outside the meeting rooms. The staff liked them, even though they were notoriously inaccurate, and they liked the way the form was laid out. They were used to it. The new software, although it included a report writer, proved incapable of mimicking the layout of the old familiar scheduling form. And it had some other wrinkles that the company didn't care for. So, its representatives came to my company and asked for a custom system. We came to terms, so we thought, and started on the design work. But every time we got close to freezing the model, our client's representative would show up like Columbo, wearing a tattered old raincoat, smoking a foul old stogie, waving one hand in the air, and calling "Just one more thing …." By the time we were through, our client had spent four times as much as it had on the off-the-shelf software. But it had a nearly bulletproof system (no system is completely bulletproof) that did a lot more than we'd originally anticipated. The system relied on the Excel interface for user input because of the flexibility of the worksheet and the wealth of ways that it can be formatted. To store the data, it relied on an external database. To attempt to keep upward of 7,000 reservations per year, with 55 fields each, in an Excel workbook would get us right back to one of the situations that was discussed in Chapter 1, "Misusing Excel as a Database Management Tool." So, we have an application that relies on an object library, VBA-driven interface between Excel and a database. Information about a new reservation is entered into Excel and stored in the database. A full day's worth of reservations are visible on an Excel worksheet, making it easy for the user to see what rooms are available at what times on any given calendar date. If information about a reservation needs to be changed, it's easily recalled from the database into the Excel workbook for modification, and the changes stored back to the database. And of course the report, the one that gets posted on all the meeting room doors, is generated automatically every Friday afternoon, in the old layout that's so favored by the company's staff. You're invited to look over the developer's shoulder in the following case study to see exactly how information that's stored in the database is returned to the Excel workbook in some very particular ways ways that make it necessary to tailor the process by way of recordsets, rather than by way of SQL. You'll also see how the data is used to populate a user form, so that the user can edit existing data. In Chapter 12, "Getting Data from Excel and into Access with ADO," you'll see how data is automatically moved from a workbook to a database. |
