YES .. I have come around to your way of thinking and prior advice along those lines .. I worked a good bit yesterday trying to retro-fit my existing code and I came up with this simple solution ..
My existing code looks like this
Code: Select all
xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cDEFA+"\Groom.mdb"
xPASSWORD := "xxxxxxxxx"
IF xDATABASE = "A" // ms access
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
ELSE
xCONNECT := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
ENDIF
...
...
oRsUser := TOleAuto():New( "ADODB.Recordset" )
oRsUser:CursorType := 1 // opendkeyset
oRsUser:CursorLocation := 3 // local cache
oRsUser:LockType := 3 // lockoportunistic
cSQL := "SELECT * From [Staff] Order by [Lname]"
TRY
oRsUser:Open(cSQL,xCONNECT )
CATCH oErr
MsgInfo( "Error in Opening Staff table" )
RETURN(.F.)
END TRY
Code: Select all
// defined at top of Main()
xPROVIDER := "Microsoft.Jet.OLEDB.4.0"
xSOURCE := cDEFA+"\Groom.mdb"
xPASSWORD := "xxxxxxxxx"
IF xDATABASE = "A" // access
xSTRING := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
ELSE
xSTRING := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD
ENDIF
xConnect := CREATEOBJECT( "ADODB.Connection" )
TRY
xConnect:Open( xString )
CATCH oErr
Saying := "Could not open a Global Connection to Database "+xSource
MsgInfo( Saying )
RETURN(.F.)
END TRY
...
...
// using the same Open() code
oRsUser := TOleAuto():New( "ADODB.Recordset" )
oRsUser:CursorType := 1 // opendkeyset
oRsUser:CursorLocation := 3 // local cache
oRsUser:LockType := 3 // lockoportunistic
cSQL := "SELECT * From [Staff] Order by [Lname]"
TRY
oRsUser:Open(cSQL,xCONNECT ) // xConnect is now the connection object
CATCH oErr
MsgInfo( "Error in Opening Staff table" )
RETURN(.F.)
END TRY
I must admit, I am a bit concerned about maintaining a single ( application ) connection due to the possibility the workstation could temporally lose its network connection and the app would not be able to re-connect, however I have all my recordsets trapped between Try,Catch and EndTry .. and all the user would see is a connection message and the app would not necessarily crash with a run-time error.... but if the network goes down or 'hick-ups' more than my app would crash as well
I would presume all my legacy applications using Ms Sql would suffer from the same connection problem and curiously why none of the DBA's would have noticed an excessive amount of open connections ? .. and even how Ms Sql manages those as concurrent or not
Rick Lipkin