It started out as such a small little project.  Given some data in VBA code, how can we get it into Excel efficiently?  I demonstrated that by using the Excel SDK and writing a C .dll, we could dramatically speed up data transfer.  After that, the questions started to fly: could we use VBA solely for the user interface?  could the data be flexibly presented? what about additional data sources?  What happens when we move to Win95?

A simple solution would have been to keep augmenting the .dll to add additional capabilities.  I determined to design an architecture that would let us grow flexibly.  I knew that we wanted to support both ODBC databases and non-ODBC databases, so I started by modeling after ODBC.   All modules were coded with both 16- and 32-bit implementations in mind, so porting was a breeze.

I divided the functionality into several independent layers.   This architecture meant we could quickly and easily extend the functionality of the Data Access Engine.  A simplification of the architecture of the DAE can be represented:

The layers provide the following functionality:

Application Interface Layer : responsible for the interface with external applications.  Currently, Excel and VB are supported. AIL drivers are responsible for collecting inputs, organizing the inputs in a common fashion and calling the Data Retrieval Layer.  The AIL builds an array that is then shared across all layers of the DAE.  It also provides hooks to functions that can be called from within the DAE.  For example, the DAE can provide status information (number of rows retrieved, etc.) if the AIL provides an appropriate hook.

Data Retrieval Layer : responsible for managing the data retrieval process.  This is code used regardless of the database involved.  The DRL provides pseudo-ODBC functions substituting for SQLConnect, SQLExecDirect, etc.  These functions then call either the ODBC equivalents for ODBC databases or core non-ODBC functions within the Database Driver Layer.

 Database Driver Layer : drivers that retrieve data from specific datasets.  For ODBC databases, these are merely standard ODBC drivers.  For non-ODBC databases, these are customized drivers that mimic ODBC functionality.  Non-ODBC drivers consist of core code common to all non-ODBC drivers and database-specific code to handle differences between databases.  Examples include the use of Sybase drivers to download mainframe data and Aremos .dlls to download Aremos data.

 Output Layer : these drivers write row / column results in a particular format.  Excel spreadsheets, Aremos banks (both “small” and “large” model) and tab-delimited text files are supported.  Called from the DRL every time a group of rows is ready for output, these drivers transmit a group of rows.

This architecture worked well over time.  For example, we added SQL Server to the Output Layer in less than one week.

More importantly, I worked to modify the DAE as part of a large-scale project to provide a standard mechanism for retrieving information at the IMF.  Users could select data across a wide variety of sources and have it returned to Excel spreadsheets and Aremos time series databanks.  This project replaced one proposed by outside contractors which would have cost tens of millions of dollars.