Jon,

The best way to handle this is to use abstract connection, data and idgenerator classes that handle differences internally but provide the same exposed methods and properties (interface) to the interface or business tier. This may require extensive redesign if you touch data from within the interface. Such as:

USE table_name
SELECT table_name
APPEND BLANK
REPLACE ID WITH lnID
REPLACE name WITH lcName
llSaved = TABLEUPDATE(.t.,.f.,table_Name)

Rather than:

loData = loBusiness.GetDataObject('table_name')
loDATA.ADD()
loRS = loDATA.GetRS()
loRS.ID = lnID
loRS.Name = lcName
loData.SetRS(loRS)
llSaved = loDATA.SAVE()

The data class code is pretty much the same for all data sources other than the Open method. I also have a TranslateSQL method where I change any Built-in functions depending on the backend. For example I have an application that runs against both VFP database and Oracle backend. In the VFP dataclass's TranslateSQL method:

lcSQL = STRTRAN(lcSQL,"SYSDATE","DATE()")

The biggest differences are in the connection and idgenerator classes. For example, my connection class creates a temporary local VFP database and local views on-the-fly when running against Visual FoxPro data in order treat it as a client/server data source. Methods for handling transactions are also completely different code.

My framework runs the same interface, business code and SQL strings for Access, VFP, SQL Server and Oracle data sources. The difference is the classes used for each data source and the internal code in those classes. Info Digger found here in the download section or at http://www.bdsoftware.com/InfoDigger.asp is built on the framework.

>While researching what is involved to upsize/migrate our application to use a SQL Server back end, I recalled that that my colleague wanted to achieve having a single set of code that supported the use of either SQL or VFP as the data engine (depending on the client site). I think the reasoning for this is because we cannot anticipate all of our clients wanting to migrate to the SQL version at the same time.
>
>I was curious if anyone here has had experiences (be they good or bad) in the scenario described above. Are we looking for headaches?
>
>Any insight would be greatly appreciated.
>
>Laterness,
>Jon