Data Access Layer
The most common question I get asked by Visual Basic developers is, "What type of DAL should I use? Data Access Objects (DAO), Open Database Connectivity (ODBC) API, ODBCDirect, Remote Data Objects (RDO), ActiveX Data Objects (ADO), VBSQL, vendor-specific library, smoke signals, semaphores, Morse code, underwater subsonic communications?" The answer is never easy. (Although in windier climates I would advise against smoke signals.)
What I do think is a good approach to data access is to create a simple abstract data interface for your applications. This way, you can change your data access method without affecting the other components in your application. Also, a simple data interface means that developers have a much lower learning curve and can become productive more quickly.
Remember that we are trying to create a design pattern for business object development in corporate database-centric applications. These applications typically either send or receive data or they request a particular action to be performed; as such, a simple, straightforward approach is required. I like simplicity-it leads to high-quality code.
So am I saying don't use ADO? No, not at all. Most Microsoft-based solutions are well suited to using ADO inside the DAL. But by concentrating your ADO code (or any database connection library for that matter) into one component, you achieve a more maintainable, cohesive approach. Besides, ADO is still a complex beast. If you take into account that many of ADO's features are restricted by the type of database source (such as handling text fields and locking methodologies), you'll find that the benefits of a stable, easily understood DAL far outweigh the cost of slightly reinventing the wheel.
Not only is abstracting the data access methodology important, but abstracting the input and output from this component are important as well. The DAL should return the same data construct regardless of the database source.
But enough theoretical waffling-let's look at something real.