Hi All,

I'm playing around with ADO trying to update a disconnected recordset, reconnect and pass the updates back to the SQL Server Northwind DB. I'm using the OldDbFox.Utils available here in downloads to do the RS->cursor->RS. So far I have not been successful on getting the changes back to the DB.

Here's a sample of the code and I'm obviously missing something, or maybe it can't be done the way I would like...

*-- Make an OLEDB connection
oADO = CREATEOBJECT("ADODB.Connection")
oADO.ConnectionString = cMyConnectStringOLEDB
oADO.ConnectionTimeout = 30

*-- Make sure cursors are client-side
oADO.CursorLocation = 3
oADO.Open

*-- Use the OleDBFox Utilities
oOleDb = CREATEOBJECT("OleDbFox.Utils")

*-- Dynamically create the SQL, but for this sample...
lcSQLTable = "employees"
lcSQLWhere = ""
lcSQLCommand = "SELECT * FROM " + lcSQLTable + lcSQLWhere
lcSQLRS = "csr" + lcSQLTable

*-- Create recordset based on selected table
LOCAL oRS AS ADODB.Recordset
PRIVATE oRS
oRS = CREATEOBJECT("ADODB.Recordset")
oRS.ActiveConnection = oADO
oRS.LockType = 4
oRS.Source = lcSQLCommand
oRS.Open

oOleDb.RsToCursor(oRS, lcSQLRS)
*!*oRS.Close  && If I close, oOleDb.CursorToRs() fails later
oRS = .NULL.

*-- Change some data in the cursor and recreate the RS
REPLACE ALL ;
	titleofcourtesy WITH STRTRAN(titleofcourtesy,".","") ;
	IN (lcSQLRS)

*-- Recreate recordset
oRS = CREATEOBJECT("ADODB.Recordset")
oOleDB.CursorToRs(oRS, lcSQLRS)

*-- Reconnect recordset
*!*oRS.Close
oRS.ActiveConnection = oADO
oRS.Source = lcSQLTable  && Can't do this when connected

*-- Update recordset
oRS.UpdateBatch

After getting the RS to the cursor, unfortunately I have to release the original RS to get the OleDB utility to get the updated cursor back to a RS. I've tried all kinds of things to get the new RS reconnected/relinked, but can't set the souce while the RS is open. If I set the rs.source while it's closed, opening it will overwrite all of the updated values... How do you relink the RS to the correct table in the DB and reconnect so you can do the update? Can you rs.open without overwriting the current values? MSKB articles only show the process using

This can be done using VFP cursors in the middle of the process, right?


Thanks,

- Brian