The following is the story of the Application Desgin.

The client had a legacy Access MDB and it had been created through a student coop program. The application ran at a Vancouver office, required it data to be either faxed or emailed to that location where it was re-entered by a clerk. A new set of requirements were brought to my attention.

The client wanted to have a new application written, using the current application as a starting point for discussion and had an extensive list of requirements and limited budget.

The requirements for the new Registration system were a follows:

1. The program had to be simple to use and easy-client interface.
2. It had to handle data from all over the province so the data had to be centralized.
3. It had to be able to save police checks of all the workers entered into the system.
4. The application had to be able to manage pictures allow the clients to edit those pictures
5. It had to be able to print to a special printer, centrally located, that could create security badges.
6. It had to produce full month end reports and quickly. The current system took up to a day to print the month end reconciliation report.
7. The system had to track the accounting and produce audit reports suitable for the government accountants.
8. The system had to be very secure wit full transaction auditing and version control.

I was put in charge of completing the project.

First, an unused backend Server was found. The hardware was setup as a standard provincial government server class computer. It had seven drives all together, a couple mirrored drives for the operating system and a raid 5 for the data storage. On this Windows2000 OS and a MS SQL server 7 with only 12 CALs.

Second, problem to resolve was that there were only 12 SQL server connections and there were 12 offices. If a 'bound' configuration was being used just one person from one office could theoretically use all available connections. The reality was that from the 12 offices the new application had to support and over 80 individual. Adding to the mix was the consideration that the connection speeds from some of the remote sites was poor at best.

The third set of issues that had to be resolved was the deployment of the application. The program would have to be deployed through the internet as sites visits were out of the question. In addition, most of the Northern sites had no IT people that could assist if something went wrong.

A group of smaller issues had to be resolved. Pictures received at each of the locations had to be edited either locally or at a central location and then stored. Subsequently the pictures were used in creating the employee security cards. Invoicing, payments and record-keeping had to be designed. The month end reporting had to be drastically improved as current it would take a few days to complete the full process.


Two of the other important components were discovered on the default government issue desktop machines. By leveraging these common ADO and MS graphic editor a couple of the requirements could be met. ODBC drivers were initially viewed as a possible solution but were quickly rejects as they tended to require on site setup, demonstrated a degree of latency when receiving or sending a large dataset and most importantly, did have the data connection features required for fine grain data-flow control.

It was decided that the pictures would be stored externally to the database as data streams of pictures seemed to add a layer of management the resulted in poor performance.

Using 'unbound' forms in which to handle the data management required a large chuck of coding. The coding had to first, open a new connection to the server, second make the request for data, third, once the data was returned the data had to placed in each field of the lists or forms as appropriate. Finally, the connection had to be terminated as the system had not only a limited band-width but a limited connection window. Judicious management of just how much data was to be returned had to be applied. The code that made the connection leveraged Microsoft's ADO-OLE technology.

The front-end portion of the application was hosted at an accessible web site. When a new client was established, their credentials, a username and password were added to a government wide applied security access list. Then they were emailed a link to the Front-End application. The client could download this portion of the program, run it, which would setup the program and launch it on the desktop as an icon. It was simple then to run the application, enter the credentials and start working immediately. The SQL BE would use Windows Authentication to allow the users to logon.

The MS SQL back end held that a majority of the business logic that was encapsulated within parameterize stored procedures on the server. In the event that a process could not be completed due to some locked records the process would send an error code back to the caller client. The caller routine could retry or abandon the processes using a established set of rules.. Most of this resolution was managed between the server and the desktop app and would rarely require user intervention. Complex transaction processes would be queued at the serve and managed there.


The design was so successfully that application could handled over 65 users simultaneously and there were never any apparent delays or timeout. According to simulations, over 200 users could be successfully handled but the system was never tested to that level in real life. The reporting functionality was a waste improvement over the previous fore-runner application. For example, on one specific report, that used to take more than a day to print, required a calculation time of up to an hour before the first letter was printed now started in less than 1.5 seconds.... 50,000 records processed. (See a smaller sample of the code used in this portion of the design.)

My tasks of working with the client to establishing scope of the project, the requirements document, estimating the hardware, operating software, development costs, deployment and training timeline was met in less than a year. I continued working directly and in-directly with the client for another 4 years and all the stake holders felt that this project was one of the most successful ever deployed within their department.