Hi Carla
I hope this code can help you.
The most important thing is that the share propertie in the all remote views must be set in .T., so, the views can share the connection.
As you can see, I create a connections cursor to detect how many different connections I have opened in my program. To do that I use the odbcH function, because no matter how many handles you have, if you have a share connection the odbch of each view must be the same, if it´s fine, when we have to revert a transaction, you can revert the all transactions that you`ve opened, because you have a record of each one.
TRY
m.llRetorno=.F.
*********/cursor to store the connections /******************
CREATE CURSOR c_conecciones (DATABASE c(100), coneccion c(100),
handle N(4),ODBCH N(10), SOURCETYPE N(2))
=CURSORSETPROP("Buffering", 3, "c_conecciones")
*********/I check out programatically all connections in my dataenvironment/********
FOR i= 1 TO 100
x="THISFORM.DATAENVIRONMENT.cursor"+ALLT(STR(i))
Y="THISFORM.DATAENVIRONMENT.cursor"+ALLT(STR(i))+".BufferModeOverride"
z=x+".alias"
IF TYPE("&x")="O"
m.lcTable=&z
SELEC &lcTable
m.lnSourceType=CURSORGETPROP('SourceType',lcTable)
IF m.lnSourceType=2 OR m.lnSourceType=4
m.lcDataBase=CURSORGETPROP('Database',lcTable)
m.lnHandle=CURSORGETPROP('ConnectHandle',lcTable)
m.lnODBCH=SQLGETPROP(m.lnHandle,"ODBChdbc")
SELECT c_conecciones
IF m.lnODBCH <> c_conecciones.ODBCH
APPEND BLANK
REPLACE c_conecciones.DATABASE WITH ALLTRIM(m.lcDataBase)
REPLACE c_conecciones.coneccion WITH ALLTRIM(m.lcConeccion)
REPLACE c_conecciones.handle WITH m.lnHandle
REPLACE c_conecciones.ODBCH WITH m.lnODBCH
REPLACE c_conecciones.SOURCETYPE WITH m.lnSourceType
ENDIF
ENDIF
ENDIF
ENDFOR
SELECT c_conecciones
*********/Set in manual all transactions /*****
SCAN
SQLSETPROP(c_conecciones.handle, 'Transactions', 2)
SQLExec(c_conecciones.handle, 'BEGIN TRANSACTION' )
ENDSCAN
**************/ update all views in data environment /**************
****************/with buffer =5 programatically/***************
***************/ if I have an error the throw command/***********
**********/ send the proccess flow to catch /**************
m.lcNombreTablas=''
m.lnModified=0
FOR i= 1 TO 100
x="THISFORM.DATAENVIRONMENT.cursor"+ALLT(STR(i))
Y="THISFORM.DATAENVIRONMENT.cursor"+ALLT(STR(i))+".BufferModeOverride"
z=x+".alias"
IF TYPE("&x")="O"
m.lcTable=&z
SELEC &lcTable
IF &Y=5
IF NOT TABLEUPDATE(1,.T.,m.lcTable)
= AERROR(aErrorArray)
IF aErrorArray[1]=1526
THROW aErrorArray[3]+"(1) El error se originó
actualizando: "+m.lcTable
ELSE
THROW aErrorArray[2]+"(1) El error se originó
actualizando: "+m.lcTable
ENDIF
ENDIF
ENDIF
ENDIF
SELECT &lcTable
ENDFOR
SELECT c_conecciones
SCAN
***********/If all was fine commit the transaction/********
m.lnCommit=SQLExec( c_conecciones.handle, 'IF @@TRANCOUNT > 0
COMMIT' )
IF m.lnCommit=-1
THROW "Transacción no se pudo actualizar"
ENDIF
ENDSCAN
m.llRetorno = .T.
CATCH TO loException
**********/rollback the transactions I´ve opened /**********
*******/ create the structured error message /***********
SELECT c_conecciones
SCAN
IF THISFORM.Tipo_vista='SQL'
=SQLExec( c_conecciones.handle, 'IF @@TRANCOUNT > 0 ROLLBACK' )
ELSE
=SQLROLLBACK(c_conecciones.handle)
ENDIF
ENDSCAN
MESSAGEBOX(Exception_Error(loException),0+16,THISFORM.CAPTION)
m.llRetorno = .F.
FINALLY
***********/ return to automatic all transactions /*******
SELECT c_conecciones
SCAN
=SQLSETPROP(c_conecciones.handle, 'Transactions', 1)
ENDSCAN
ENDTRY
RETURN m.llRetorno
As you can see we could have mora than one transaction, I something was wrong you can rollback each transaction.
Carlos A. Miranda
E.I.S.lnc
President