I'd use Option 3, but with one change. Use OLEDB instead of ODBC.

>I would like to know your point of view on this design. I need to perform a series of treatments on the majority of the fields in a table. On some treatments, all records are to be treated. The table can contains up to 500 000 records. The design must be flexible so that I could change the data layer to use SQL Server in a near future without making any change in the other tiers.
>
>Using VFP 8.0, the user interface use the midle-tier (COM) which in turn use the data layer (COM).
>
>I have tought of these possibilities:
>
>1) The data layer (DL) perform a SELECT-SQL on the table, transform the cursor in a ADO recordset and return the recordset to the midle tier (MT). The MT do the modifications and return the recordset to the DL which will save it to the VFP table
>
>Pros:
>- Faster than XML
>- Not limited by ODBC limitations
>- One call to get the data from the DL and one call to save the data
>
>Cons:
>- It takes a lot of time and memory to select and transform the data into and from ADO when working with a large table
>
>2) Same as 1, but with XML
>
>Pros:
>- Could be made as a web service, but I don't see what advantage this will give me
>- Not limited by ODBC limitations
>- One call to get the data from the DL and one call to save the data
>
>Cons:
>- A lot slower than option 1
>
>3) Same as 1, but without the SELECT-SQL. ADO will connect to the table with ODBC (I assume this can be done, I haven't tested it)
>
>Pros:
>- Don't have to transform the data back and forth from cursor to ADO
>- One call to get the data from the DL and can save the data directly from the recordset because it is bound to the table
>
>Cons:
>- I have bad experience with ODBC. Not all VFP commands are available, so I have to make sure I don't use them in any triggers or store procedures
>- Performance?
>
>4) The DL expose the data manipulation commands (like SKIP, GetField, LOCATE, etc.) and act directly on the table
>
>Pros:
>- Don't have to transform the data back and forth from cursor to ADO
>- Not limited by ODBC limitations
>- Eat up less memory
>
>Cons:
>- A lot of calls between the MT and DT to loop through the data
>
>What options would you take and why? Any other options I am missing?
>
>All suggestions are welcome.
>
>TIA