Microsoft KB Archive/191413

= How To Use Offline ADO Recordsets in Visual FoxPro =

Article ID: 191413

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q191413



SUMMARY
This article describes how to use offline ActiveX Data Objects (ADO) 2.x recordsets in Visual FoxPro 6.0.



MORE INFORMATION
The following code example creates an ADO recordset and opens the recordset using an existing data source, makes a change to the data, and updates all the records that have been updated: LOCAL lcConnStr,lcSQL,oConnection,rs

lcConnStr="DSN=Visual FoxPro Database;UID=;PWD=;"+ ; "SourceDB="+HOME(2)+"data\Testdata.dbc;"+ ; "SourceType=DBC;Exclusive=No;BackgroundFetch=No;Collate=Machine;" lcSQL="select * FROM orders.dbf WHERE To_Region='WA'"

oConnection=CREATEOBJECT("ADODB.Connection") oConnection.open(lcConnStr) rs=oConnection.execute(lcSQL) rs.close rs.cursorType=3         && 3=ADOPENSTATIC rs.cursorLocation=2     && 2=ADUSESERVER rs.lockType=3           && 3=ADLOCKOPTIMISTIC rs.open MESSAGEBOX("Record 1 (before):"+CHR(13)+CHR(13)+ ;     "City: "+rs.fields("To_City").value+CHR(13)+ ;      "Order Amount: "+TRANSFORM(rs.fields("Order_Amt").value)) rs.fields("To_City").value="Bellevue" rs.fields("Order_Amt").value=rs.fields("Order_Amt").value+1 rs.updateBatch rs.close rs.open MESSAGEBOX("Record 1 (after):"+CHR(13)+CHR(13)+ ;     "City: "+rs.fields("To_City").value+CHR(13)+ ;      "Order Amount: "+TRANSFORM(rs.fields("Order_Amt").value)) rs.close oConnection.close RETURN

The results of the preceding program code can be checked by issuing the following commands from the Visual FoxPro Command window:

USE HOME(2)+"data\Orders" BROWSE FOR To_Region="WA" Look at the Order_amt column for the first record in the Browse window.

In order to persist ADO recordsets, you must have Microsoft Data Access Components (MDAC) version 2.x or later installed, which is included in the data components of Visual Studio 6.0 or can be downloaded from the following Web address:

http://msdn.microsoft.com/dataaccess

Here is sample code to persist(save) a recordset: LOCAL lcConnStr,lcSQL,oConnection,rs,lcFileName

lcFileName=LOWER(SYS(2023))+"\orders.rs" lcConnStr="Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;"+ ; "SourceDB="+HOME(2)+"data\Testdata.dbc;"+ ; "SourceType=DBC;Exclusive=No;BackgroundFetch=No;Collate=Machine;" lcSQL="select * FROM orders.dbf WHERE To_Region='WA'"

oConnection=CREATEOBJECT("ADODB.Connection") oConnection.open(lcConnStr) rs=oConnection.execute(lcSQL) rs.close rs.cursorType=3         && 3=ADOPENSTATIC rs.cursorLocation=3     && 3=ADUSECLIENT rs.lockType=3           && 3=ADLOCKOPTIMISTIC rs.open ERASE (lcFileName) rs.save(lcFileName) rs.close oConnection.close RETURN Look for Orders.rs in the Temp directory after running this code. The SYS(2023) function returns the path to where Visual FoxPro stores its temporary files which is the Windows' temporary directory by default.

NOTE: If you get the following error message running the preceding code, check the lcConnStr variable:

OLE IDispatch exception code 0 from Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager]Data Source name not found and no default driver specified.

