Re: Persisted Recordsets

From: Patrick Bates (nomail_at_microsoft.com)
Date: 08/04/04

  • Next message: Doug Stoltz: "Re: Error -2147168227 Cannot create new transaction because capacity was exceeded." 
    Date: Wed, 4 Aug 2004 15:23:52 -0500

    I should probably mention that I'm using ADO 2.7

    Here's an error I'm getting when I try to save the recordset and disconnect. First, the code:

                If fso.FileExists("c:\timeclock.errors") Then
                    fso.DeleteFile "c:\timeclock.errors"
                End If
                timeclock_errors.Save "c:\timeclock.errors", adPersistADTG
                timeclock_errors.ActiveConnection = Nothing

    When the last line executes, I'm told the operation is not allowed when the object is Open. FYI, the
    recordset was opened as Keyset, BatchOptimistic.

    Any suggestions? Because once I do it my way with the close and reopen, the Resync command fails claiming
    that there is not enough information about the original query to reconnect to the datasource when I try to
    call Resync after setting the ActiveConnection.

    "Val Mazur" <group51a@hotmail.com> wrote in message news:eo$UJYdeEHA.644@tk2msftngp13.phx.gbl...
    > Hi Patrick,
    >
    > Based on your code, you are not disconnecting recordset, but killing it
    > completely. To disconnect recordset from the database, you have to make two
    > steps. First is to open recordset on a client side and, second, set
    > ActiveConnection property of it to Nothing. To connect it back to the
    > database, you would need to set ActiveConnection property to the actual
    > opened connection
    >
    > If fso.FileExists("c:\timeclock.errors") Then
    > fso.DeleteFile "c:\timeclock.errors"
    > End If
    > timeclock_errors.Save "c:\timeclock.errors", adPersistADTG
    > Set timeclock_errors.ActiveConnection = Nothing
    > 'You do not have to re-open saved recordset here, because it is still in
    > memory
    >
    >
    > 'Use Set to point to the opened connection, otherwise conection could be
    > closed
    > Set timeclock_errors.ActiveConnection = GetDSN()
    > timeclock_errors.Resync
    >
    >
    > --
    > Val Mazur
    > Microsoft MVP
    >
    >
    > "Patrick Bates" <nomail@microsoft.com> wrote in message
    > news:O4JF%23$LeEHA.3632@TK2MSFTNGP11.phx.gbl...
    > > I'm writing an application where I'm trying to compensate for loss of
    > > connection to a SQL Server by persisting
    > > the recordsets to disk if the connection is determined to be unavailable,
    > > then reconnect the recordsets once
    > > the connection is determined to be available again. I'm having trouble
    > > disconnecting and reconnecting
    > > BatchOptimistic recordsets on the fly, in fact I've not been able to do it
    > > at all. Here's some code I'm using
    > > to work around this...
    > >
    > > This first set is from the function which first determines if the SQL
    > > Server is unavailable:
    > > If fso.FileExists("c:\timeclock.errors") Then
    > > fso.DeleteFile "c:\timeclock.errors"
    > > End If
    > > timeclock_errors.Save "c:\timeclock.errors", adPersistADTG
    > > Set timeclock_errors = Nothing
    > > Set timeclock_errors = New ADODB.Recordset
    > > timeclock_errors.Open "c:\timeclock.errors", , adOpenKeyset,
    > > adLockBatchOptimistic
    > >
    > > Is there an easier, cleaner way to get the recordset disconnected and keep
    > > it open?
    > >
    > > This set is from the function which determines if the SQL Server is now
    > > available:
    > > timeclock_errors.ActiveConnection = GetDSN()
    > > If timeclock_errors.Status = adRecPendingChanges Then
    > > timeclock_errors.UpdateBatch
    > > End If
    > > timeclock_errors.Resync
    > >
    > > This code is a total wreck. As far as I can tell, the Resync command will
    > > fail all the time, so I'm not sure
    > > how to refresh all the data in the recordset, assuming the connection
    > > could have been down for an extended
    > > period. Also, the Status check is failing if the first set of code was
    > > just executed and BOF is still true.
    > >
    > > Is there some way to detect across the entire recordset if UpdateBatch
    > > needs to be called, without having to
    > > walk every record and check for changes?
    > >
    > > Thanks,
    > > Patrick
    > >
    > >
    >
    >

  • Next message: Doug Stoltz: "Re: Error -2147168227 Cannot create new transaction because capacity was exceeded."

    Relevant Pages
    Loading