HOWTO: Use Offline ADO Recordsets in Visual FoxPro

ID: Q191413

The information in this article applies to:

Microsoft Visual FoxPro for Windows, version 6.0
Microsoft Data Access Components version 2.1 SP2

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://www.microsoft.com/data/

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.

REFERENCES

For additional information, please see the following World Wide Web URL:

http://www.microsoft.com/data/ado/

For more information on ensuring that you have a valid connection string, see the following article in the Microsoft Knowledge Base, copy the Connect String from the Connection Designer and paste it into the preceding code for the lcConnStr variable:

Q186086 HOWTO: Get a File DSN in the Connection Designer

Additional query words: PGFEST600 kbVFp600 kbADO kbADO200

Keywords : kbDatabase kbGrpFox kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:2.1 SP2,6.0
Platform : WINDOWS
Issue type : kbhowto