[AccessD] Disconnected recordset as form source?

You might consider saving the recordset as a persisted one rather than
using a table at all.  I've got a couple of old samples at Roger's Access
Library that demonstrate using persisted recordsets and disconnected
recordsets in Access 2000,  Take a look here at the PersistedRecordset and
NoTables samples and see if anything helps:

http://www.rogersaccesslibrary.com/forum/foust-charlotte_forum14&SID=f27a794a-684c-f2495918-e4c25924-463bez2z.html

Charlotte Foust

On Sat, Mar 24, 2012 at 11:18 AM, <roz.clarke at barclays.com> wrote:

> Hello all!
>
> I'm doing a very rare, for me, bit of work on a database, and I'm trying
> to make a continuous form give me extended multiselect functionality, so
> that the users can use filter by form etc., then choose a bunch of
> records to perform an action on. They need to be able to select
> discontiguous records. I'm finding it a bit challenging...
>
> The back end will eventually be on SQL Server but I'm doing a demo
> version with an Access 2000 back end.
>
> I can see that I could do it by copying the working data into a new,
> local, table, adding a 'selected' column, and using this, bound to my
> continuous form, to identify the records for manipulation. However to
> synchronise with other user updates (there will be a couple hundred
> people writing updates) that table's going to get deleted and re-created
> every two minutes which is going to cause a bit of bloat (it's got about
> 100k rows in it).
>
> I had thought that I could achieve the same thing with disconnected
> recordsets. On paper it works elegantly; pull the PK & other key fields
> + the 'selected' field through at the start of the session, bind a
> search form to the recordset and drop the connection, have the users
> search for the records they want to work on, let them make their
> selection of rows to go ahead and edit, and only then do I pop them into
> a bound form based on their pick of records. No changes in the
> disconnected rst ever need to be written back to the table.
>
> This is the code opening the rst, binding the form and dropping the cnn:
>
> Dim cnn As New ADODB.Connection
> Dim rstExceptions As New ADODB.Recordset
>
>
> Private Sub Form_Open(Cancel As Integer)
>
> Set cnn = CurrentProject.Connection
>
> rstExceptions.CursorLocation = adUseClient
> rstExceptions.Open "SELECT * from tblUnmatched_Combined", cnn,
> adOpenKeyset, adLockBatchOptimistic
>
> Set Me.Recordset = rstExceptions
>
> Set rstExceptions.ActiveConnection = Nothing
> cnn.Close
>
> End Sub
>
> BUT OF COURSE it doesn't quite work; I'm getting the error message "This
> record has been changed but the updated data will not be displayed
> because it doesn't satisfy the criteria of the underlying recordsource."
>
> It's random; you can update 3 rows happily and then one fails, come back
> to that one a few seconds later and it succeeds. So it's *not* the data.
> The 'help' says this can be a problem you get with SQL Server
> recordsets, but this ain't a SQL Server recordset so...
>
> Anyone tried something like this and willing to chew it over with me?
>
> It's good to be back.
>
> Roz
> This e-mail and any attachments are confidential and intended
> solely for the addressee and may also be privileged or exempt from
> disclosure under applicable law. If you are not the addressee, or
> have received this e-mail in error, please notify the sender
> immediately, delete it from your system and do not copy, disclose
> or otherwise act upon any part of this e-mail or its attachments.
>
> Internet communications are not guaranteed to be secure or
> virus-free.
> The Barclays Group does not accept responsibility for any loss
> arising from unauthorised access to, or interference with, any
> Internet communications by any third party, or from the
> transmission of any viruses. Replies to this e-mail may be
> monitored by the Barclays Group for operational or business
> reasons.
>
> Any opinion or other information in this e-mail or its attachments
> that does not relate to the business of the Barclays Group is
> personal to the sender and is not given or endorsed by the Barclays
> Group.
>
> Barclays Bank PLC. Registered in England and Wales (registered no.
> 1026167).
> Registered Office: 1 Churchill Place, London, E14 5HP, United
> Kingdom.
>
> Barclays Bank PLC is authorised and regulated by the Financial
> Services Authority.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>