There are no quick answers to this, but I think the key ingredient to make this happen is a good business object architecture. It will not get you there 100% as different backends have different SQL syntax, but it will get you there most of the way and you can isolate backend specific code relatively easily.

Althougy VFP views are a pretty good mechanism to get back end agnostic data access views generally aren't a great implementation due to the problems they present when data changes etc.

A good business object framework will have standard methods for doing common things like retrieving records, updating, saving and deleting data so all that's left is to do application sopecifc SQL for reports etc. There is where you will likely find the biggest variance between sql backends and you will have to know what you're targeting to account for all scenarios. Luckily SQL syntax (if you don't start with VFP's syntax that is) is fairly universal especially if you stick to ANSI standards as much as possible.


+++ Rick ---



>This is a really big question: How do you design a VFP program so that it is easy to change what data store it uses?
>
>I'd like to design a new program I'm working on so that it normally uses DBF's, but can be switched to use SqlServer, MSDE or MySql as a back end.
>
>I think many may jump at the quick answer "Use views in a DBC"... but I don't think that's a complete answer:
>1) What do you have to do to a view to adjust it from accessing DBF's to accessing MySQL or SqlServer?
>2) How do you manage using SQLEXEC for running SQL UPDATE and INSERT commands where, I think, the syntax differs from one DBMS to another?
>3) How do you deal with the different Type identifiers in CREATE TABLE commands?
>4) How do you generate Primary Keys (VFP6) where VFP must pre-calculate them, and Most RDBMS's have auto-increment fields? (similarly, after adding a new record in an RDBMS, how do you retrieve that auto-generated PK?) A Stored Procedure might simplify things here, even in VFP on an INSERT trigger, but I want to avoid SP's... I want the App to create the DB from scratch (which doesn't necessarily preclude SP's, which it could programmatically create....)
>
>
>Another "quick answer" many might want to say is "Use business objects"... however, that just avoids the question...: How do you design business objects that handle the above issues?
>
>I'm sure I'll come up with more questions along this line as I work on this... thanks everyone for considering it!