So from what you are saying, I need to open the connection somewhere before I open any forms and leave it open until I close the app. Where would you recommend opening the connection?

So I would open the connection somewhere, store the connection handle in a property. I would set my CA's to have a datasource of the connection number and modify the sqlstring connect with .T. for the shared field).


At current I have not opened a seperate connection. I a, letting the CA's handle them. But I have the .T. set for shared. I am however getting my update hanging on the 4th CA in a transaction. I am currently handling the update process almost identically to how I was doing views with setting the transaction to manual, then in a transaction I do table updates followed by sqlcommits.

* Function:		Update_Views
* Description:	Update Views (begin transaction, tableupdates, sqlcommits, endtransaction,
*				rollback, sqlrollback, tablerevert)

EXTERNAL ARRAY paUpdateViews

DIMENSION nConnectionHandles[99]

LOCAL lnCounter, nStages, lnRecordCount, lnCurrentRecord, lnMaxConnectionHandle, lnConnection

lnMaxConnectionHandle = 0
nStages = pnNumber_of_Views + 1

* load SourceType and ConnectHandle for Views, and set manual transactions for remote views
FOR lnCounter = 1 TO pnNumber_of_Views
	paUpdateViews(lnCounter, 2) = CURSORGETPROP("SourceType", paUpdateViews(lnCounter, 1))
	IF paUpdateViews(lnCounter, 2) = 102
		lnConnection = CURSORGETPROP("ConnectHandle", paUpdateViews(lnCounter, 1))
		IF lnMaxConnectionHandle = 0
			lnMaxConnectionHandle = 1
			nConnectionHandles[1] = lnConnection
			SQLSETPROP(lnConnection, "Transactions", 2)
		ELSE
			IF ASCAN(nConnectionHandles, lnConnection) = 0
				lnMaxConnectionHandle = lnMaxConnectionHandle + 1
				nConnectionHandles[lnMaxConnectionHandle] = lnConnection
				SQLSETPROP(lnConnection, "Transactions", 2)
			ENDIF
		ENDIF
		paUpdateViews(lnCounter, 3) = lnConnection
	ELSE
		paUpdateViews(lnCounter, 3) = 0
	ENDIF
ENDFOR

* update the import files
BEGIN TRANSACTION

IF NOT EMPTY(paUpdateViews(1, 4)) AND TYPE('oProgressWindow') = 'O'
	oProgressWindow.update(0, "Committing Changes to Database")
	oProgressWindow.clearmessage2
	oProgressWindow.clearmessage3
ENDIF

FOR lnCounter = 1 TO pnNumber_of_Views
	IF NOT EMPTY(paUpdateViews(lnCounter, 4)) AND TYPE('oProgressWindow') = 'O'
		oProgressWindow.update(lnCounter * 100 / nStages,, paUpdateViews(lnCounter,4))
	ENDIF
	SELECT (paUpdateViews(lnCounter, 1))
	cSaveOrder = ORDER()
	SET ORDER TO
	cSaveFilter = FILTER()
	SET FILTER TO
	SET ORDER TO paUpdateViews(lnCounter, 6)

	IF BOF() OR EOF()
		llDoLocate = .T.
	ELSE
		llDoLocate = .F.
		lcKeyExpression = KEY()
		lKeyValue = &lcKeyExpression
	ENDIF

	llUpdateOK = TableUpdate(.T., .T., paUpdateViews(lnCounter,1))
	IF NOT llUpdateOK
		=AERROR(aErrorArray)
		=MESSAGEBOX(paUpdateViews(lnCounter, 5) + CHR(13) + STR(aErrorArray(1)) ;
			+ CHR(13) + aErrorArray(2))
		EXIT
	ENDIF

	SELECT (paUpdateViews(lnCounter, 1))
	IF llDoLocate
		LOCATE
	ELSE
		IF NOT SEEK(lKeyValue, paUpdateViews(lnCounter, 1), paUpdateViews(lnCounter, 6))
			LOCATE
		ENDIF
	ENDIF
*!*		IF lnCurrentRecord > 0 AND lnCurrentRecord <= lnRecordCount
*!*			GOTO lnCurrentRecord
*!*		ENDIF
	IF NOT EMPTY(cSaveOrder)
		SET ORDER TO &cSaveOrder
	ENDIF
	IF NOT EMPTY(cSaveFilter)
		SET FILTER TO &cSaveFilter
	ENDIF
*!*		IF lnCurrentRecord = 0 OR lnCurrentRecord > lnRecordCount
*!*			LOCATE
*!*		ENDIF
ENDFOR

IF NOT EMPTY(paUpdateViews(1, 4)) AND TYPE('oProgressWindow') = 'O'
	oProgressWindow.update(100)
ENDIF

IF llUpdateOK
	IF lnMaxConnectionHandle > 0		&& if we have any remote data, do sql commits
		LOCAL lnOK
		* Commit transactions for remote views
		FOR lnCounter = 1 TO lnMaxConnectionHandle
			lnOK = SQLCOMMIT(nConnectionHandles[lnCounter])
			IF lnOK # 1
				=AERROR(aErrorArray)
				=MESSAGEBOX("Remote Data commit failed on Connection Handle " ;
					+ ALLTRIM(STR(nConnectionHandles[lnCounter])) + CHR(13) + STR(aErrorArray(1)) ;
					+ CHR(13) + aErrorArray(2))
				EXIT
			ENDIF
		ENDFOR
		
		* If the remote commits failed, rollback all connections
		IF lnOK # 1
			FOR lnCounter = 1 TO lnMaxConnectionHandle
				SQLROLLBACK(nConnectionHandles[lnCounter])
			ENDFOR
			ROLLBACK
			FOR lnCounter = 1 TO pnNumber_of_Views
				TABLEREVERT(.T., paUpdateViews(lnCounter, 1))
			ENDFOR
			llUpdateOK = .F.
		ELSE
			END TRANSACTION
		ENDIF
	ELSE	&& when no remote data, end transation
		END TRANSACTION
	ENDIF
ELSE	&& if any of the tableupdates failed, rollback and revert
	* Rollback transactions for remote views
	IF lnMaxConnectionHandle > 0
		FOR lnCounter = 1 TO lnMaxConnectionHandle
			SQLROLLBACK(nConnectionHandles[lnCounter])
		ENDFOR
	ENDIF
		
	ROLLBACK    && end the transaction and revert the transaction buffer
	*** The transaction buffer will be rolled back,
	*** but the table/view buffers will still be dirty
	*** so either revert or have you own error handling
	*** try to fix the problem.

	FOR lnCounter = 1 TO pnNumber_of_Views
		TABLEREVERT(.T., paUpdateViews(lnCounter, 1))
	ENDFOR
ENDIF

* Reset to automatic transaction mode for remote views
FOR lnCounter = 1 TO lnMaxConnectionHandle
	SQLSETPROP(nConnectionHandles[lnCounter], "Transactions", 1)
ENDFOR

DIMENSION cSaveOrder[pnNumber_of_Views], cSaveFilter[pnNumber_of_Views], ;
	nSaveRecordNumber[pnNumber_of_Views], lDoLocate[pnNumber_of_Views], ;
	KeyExpression[pnNumber_of_Views], KeyValue[pnNumber_of_Views]

FOR lnCounter = 1 TO pnNumber_of_Views
	SELECT (paUpdateViews(lnCounter, 1))
	cSaveOrder(lnCounter) = ORDER()
	cSaveFilter(lnCounter) = FILTER()
	SET ORDER TO paUpdateViews(lnCounter, 6)
	IF BOF() OR EOF()
		lDoLocate(lnCounter) = .T.
	ELSE
		lDoLocate(lnCounter) = .F.
		KeyExpression(lnCounter) = KEY()
		KeyValue(lnCounter) = &KeyExpression(lnCounter)
	ENDIF
*!*		nSaveRecordNumber(lnCounter) = RECNO()
	SET ORDER TO
	SET FILTER TO
ENDFOR

FOR lnCounter = 1 TO pnNumber_of_Views
	=REQUERY(paUpdateViews(lnCounter, 1))
ENDFOR

* reset record number
FOR lnCounter = 1 TO pnNumber_of_Views
	SELECT (paUpdateViews(lnCounter, 1))
	IF lDoLocate(lnCounter)
		LOCATE
	ELSE
		IF NOT SEEK(KeyValue(lnCounter), paUpdateViews(lnCounter, 1), paUpdateViews(lnCounter, 6))
			LOCATE
		ENDIF
	ENDIF
*!*		COUNT TO lnRecordCount
*!*		IF nSaveRecordNumber(lnCounter) > 0 AND nSaveRecordNumber(lnCounter) <= lnRecordCount
*!*			GOTO nSaveRecordNumber(lnCounter)
*!*		ENDIF
ENDFOR

* reset record order
FOR lnCounter = 1 TO pnNumber_of_Views
	SELECT (paUpdateViews(lnCounter, 1))
	IF NOT EMPTY(cSaveOrder(lnCounter))
		SET ORDER TO &cSaveOrder(lnCounter)
	ENDIF
ENDFOR

* reset record filter
FOR lnCounter = 1 TO pnNumber_of_Views
	SELECT (paUpdateViews(lnCounter, 1))
	IF NOT EMPTY(cSaveFilter(lnCounter))
		SET FILTER TO &cSaveFilter(lnCounter)
	ENDIF
ENDFOR

RETURN llUpdateOK

>>How do I set cursoradapters to use shared connections?
>>
>>Jason
>
>You do not do this at the CA level. The CA uses whatever connection you specify in the DataSource property or the *CmdDataSource properties [* = Insert, Update or Delete]. When you create your connection using SQLSTRINGCONNECT or SQLCONNECT pass .T. for the "sharable" parameter of these functions. The DataSource property of your CA is where you identify the connection. For example, I have GetHandle() for my DataSource property value which returns the numeric connection handle to this DataSource property at runtime.