Hi David,

First let me tell you that it is definitely possible to have the same VFP application seamlessly connect to different data backends with a little planning and careful coding. I have developed applications in this way for several years with the ability to seamlessly switch database backend from VFP DBC, SQL SERVER/MSDE, or ORACLE. Access to the data is via ODBC using remote views and SPT. Most data entry is handled using updatable remote views, and most reports use SPT read only cursors. One thing to get used to is that you don't "USE" the actual table or even a view that drags in the entire table. I use parameterized remote views or cursors to fetch only the necessary records for whatever task is at hand and use table buffering and SQL transactions. This provides very fast efficient processing in a multiuser environent, with little or no chance for corruption even when using a VFP database. You will also have to be very careful to use ANSI SQL syntax for all SQL statements and avoid database specific functions and syntax unless you carefully code case statements to handle the different backends used. Also, be aware that VFP allows you be a little sloppy with SQL syntax and the order of the key words in SQL statements such that a SPT query that runs fine against a VFP database may not work at all against a SQL Server backend. I had to learn this the hard way. Example, it took me some time to find and fix all instances such as SELECT SOMEFIELD DISTINCT FROM MYDATATABLE WHERE SOMECONDITION that runs fine on a VFP database but will not work against SQL SERVER. It should be SELECT DISTINCT SOMEFIELD...

I have a VFP DBC that contains all remote view definitions. At runtime, this views DBC is copied to each workstation's private work space and is used exclusively by that workstation to avoid data contention issues and to allow all remote views to be used exclusively. A connection is created to whatever database is being used and is stored in the views DBC. I use an ODBC system DSN to connect to the data, but I'm sure that you could just as easily use a DSNless connection. I also have to deal with possible update collisions or "dirty buffers" where more than one user may be editing the same record at the same time since I use optimistic table buffering and don't lock records in the source table, but that is pretty easy to manage.

Once you have created the remote views or SPT cursors that you need, you use them and manipulate them just like tables, SCAN, DO WHILE ..., LOCATE, etc. If you need to use seek or other functions that requires an index to be set, you will have to index the view or cursor when it is opened, but with parameterized views and proper use of SPT, you should only be pulling the records that you need and requery when your condition changes. That normally eliminates the need to index the cursors.

The advantages of using the same remote views and SPT cursors for all database backends is that you only have one code base to manage, you can seamlessly switch databases from VFP to SQL server as a user's needs change and you are not tied to a single data store whereby you can develop the application using VFP databases and deploy it using almost any ODBC compliant database server as the data store.

Disadvantages are that you have to more carefully code and test against different backends, you may have to do some workarounds to make things work as expected for all servers, and you aren't taking advantage of using stored procedures or database server specific functions and optimization. Also ODBC has some differences and limitations compared to native VFP data handling to keep in mind, and VFP database performance with ODBC is a little slower than native data table access especially over a network compared to a local machine. I guess the last one is due to the fact that VFP is not a true database server in that queries are processed on the local workstation instead of the server. With careful coding and conditional backend specific SQL statements you can even take advantage of database specific stored procedures, functionality and optimizations, but I have not personally implemented this and have not seen significant performance issues.

Updating data structures on different data servers is a little trickier but can be done with alter table commands if you have the necessary permissions on the server to do this sort of thing. Updating the remote views dbc is easy, I create a .mem file that saves the datetime of the views DBC that is copied to the workstation private work space and check to see if a newer one is available and copy it if needed. This is much like a loader program for the EXE.

Good luck with the effort. Newer versions of VFP may even make this easier with such things as cursor adapter, etc. It will be a slight shift in database access techniques but it should increase your developer skill set and allow your applications to overcome some of the negative attacks and objections normally heaped on VFP applications.

HTH