KB191413 - How To Use Offline ADO Recordsets in Visual FoxPro

Article ID: 191413

Article Last Modified on 7/13/2004

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.

REFERENCES

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

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/adostartpage1.asp

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:

186086 How To Get a File DSN in the Connection Designer

Additional query words: PGFEST600 kbVFp600 kbADO kbADO200

Keywords: kbhowto kbdatabase KB191413