Re: Persisted Recordsets

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Date: Fri, 13 Aug 2004 10:40:41 -0500

Your comment about the CursorLocation property was the key to this. Somehow, my stock Database module was set to adUseClient for
read-only recordsets, but adUseServer for read-write.

Now, I have an additional problem. My intent was to write recordsets to disk as the application is closed so that when the
application is opened again and no connection can be established with the SQL Server, that it will load the recordsets from disk.
This works well, until I try a Resync of the recordsets. I receive the following error:

Insufficient base table information for updating or refreshing.

How can I either store the base table information during the write to disk, or add it to the recordset after it has been read from
disk?

Thanks!

"Val Mazur" <group51a@hotmail.com> wrote in message news:e23XJw1eEHA.712@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> First you need to use Set, when you set to Nothing. Your line of code should
> be like
>
> Set timeclock_errors.ActiveConnection = Nothing
>
> If it does not help, then it means that your recordset is opened on a server
> side and you cannot disconnect it (see my first posting). To open recordset
> on a client side you would need to set CursorLocation of the Recordset or
> Connection (depending on how you open your recordset) to adUseClient. If you
> use Open method of ADO recordset to open it, then set CursorLocation
> property of the recordset to adUseClient. If you use Execute method of
> connection or command object, then set CursorLocation property of the
> connection to the same value and it should work
>
> --
> Val Mazur
> Microsoft MVP
>
>
> "Patrick Bates" <nomail@microsoft.com> wrote in message
> news:u2eU3DmeEHA.1656@TK2MSFTNGP09.phx.gbl...
> >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
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Relevant Pages