Architecture ideas for n-tier app with diff backends

We are in the proccess of converting our MS Access 97 based application to a 3+ tiered app that will be able to use either an Access 97, Access 2000, SQLServer7, or Oracle 8.0.5 backend. I would like to open a discussion about the caveats, tricks, and tips involved in such an architecture. Currently, we are only concerned with the data tier. I have a few ideas to fuel the fire:

1. separating the tiers into presentaion, business rules, data access, dbms
2. breaking the data access tier into a single interface for the presentation layer and possible multiple child interfaces for actually talking to the different dbms.
3. The compromise between leveraging dbms specific functionality and maintaining easy revision control for conceptually similar data access.
4. (a continuation of 3) using straight ANSI SQL in data tier components vs dbms specific stored procedure languages.

I hope this thread will provide insight for the armys of developers required to design for multiple dbms.

-Peter Received on Tue Jun 15 1999 - 13:49:08 CDT