After designing the new entry forms, I went to work updating the other database forms. Most of it was minor adjustments, like changing the entry form for the old Professor table to new Contact table. A bigger change was the staff assignment form. While it was possible to assign staff to a setup or a pickup when the class was entered into the schedule, it wasn’t always possible to know who would be available. Also, staffing changes over time and someone scheduled to handle a class for a whole semester may be out for a day or more during that semester. The assign setups form showed all of the setups and pickups scheduled for a specific day and allowed the staff assigned to each to be set or changed.

The form was a form with subform. The main form was used to select the date. The subform showed every record in the EventStaff table for that date, sortable by time, location or staff assignment. Using this form enabled the user to quickly change staffing for a day if someone took a day off or would be in at a different time than expected or if a lot of events were added for a day. Since the underlying structure of staff assignment was now completely different, this form had to be reworked for the new database. The easiest way to handle it was to show each each record in the class table for a given date. But now, instead of pickup and setup being shown on their own, they were now listed together by the class. The list was still sortable, but the time option sorted by the setup time.

This change made it more difficult to make sure that one staff member didn’t have too much going on at one time, as it wasn’t as obvious. However, the view could be resorted by staff member and checked that way. Additionally, it did make it easier to make sure that a given staff member was assigned to both the setup and the pickup of a particular class. That was something I was attempting to do whenever possible, as the staff tended to miss pickups they were assigned if they didn’t set them up. As far as I was concerned, that was an acceptable tradeoff.

What I was not prepared for was the difficulty in writing a query for the printed schedules. The reports were in place from the old database, but with the new database structure, I was unable to write a query that would give me a separate record for each setup and pickup. So, I wrote code to create a new table containing the data as I needed it to work with the existing reports. The code would first create a new table with all of the relevant information about all of the setups in the Class table. Then, all of the pickups in the Class table would be appended as new records. This effectively split the data in the Class table such that there were two records in the new table (DailyView) for each record in the Class table. A little unwieldy, but it gave me what I needed. I set up the reports and a form that shows each day’s schedule in order by time to call the code when opened. This, of course, brought an delay when opening, but as it’s a small database the delay was not an unacceptable one.

With this change, the database was fully operational. But it now needed to be accessible to other staff members. The system whereby only two people, me and the director, were able to get into the schedule was not good enough. I needed all of the full-time staff to have access. In addition, some would need to view and print only and some would need to be able to add classes and assign staff.

I had some experience setting up a networked Access database at my previous place of employment, the Syracuse University Bookstore. In that case, our department had two networked databases running: one for professors’ desk copy orders and one for student special account books. The former was used by everyone and the latter only by two people. These used the security by obscurity model. Only the people who needed access to the second database had a copy of the front-end on their machine. As for the first, everyone who had access could enter and edit anything. The only concern was that someone might accidentally break something in design view. So, the front end was distributed as a compiled MDE file, which doesn’t allow any changes to the design.

Unfortunately, this database would have to be different. I would need three levels of accounts: view, edit and administer. Setting up the workgroup on the server and creating a startup shortcut was easy once I realized what I needed to do. Setting up the accounts was easy. I replicated the database and put it on everyone’s machines. Everything was great until the staff tried to print schedules and got a permissions error. It wasn’t the backend as everyone had write permissions to the network directory it was in. It was the DailyView table. I had to give all of the accounts administrative access to that table for them to be able to run the code creating and updating the table. I fixed a copy on staff member’s machine and then copied it back to the USB drive to copy to the other machines. However, I forgot to update the copy on my computer, so I hit this problem a few more times after updating other parts of the front end. It took about four times before I realized it wasn’t reverting, I just never fixed the accounts on the master copy of the database.

By the end of summer, I had a working, networked schedule with the ability to more easily add full-semester courses. All of the full-time staff in the department now had the ability to print schedules. And I had one staff member who could add events to the schedule in my absence. But I still had a few more features I wanted to add.

Earlier parts of this series: Schedule Hacking Part One: Building an Audio-Visual Schedule
Schedule Hacking Part Two: Changes