[AccessD] Disconnected recordset as form source?

That was my thought until I saw "it's got about 100k rows in it"

-- 
Stuart

On 24 Mar 2012 at 15:52, Doug Murphy wrote:

> Not sure what the overall objective is or required functionality from the
> continuous form but a list box would make the selection part trivial.
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> roz.clarke at barclays.com
> Sent: Saturday, March 24, 2012 11:18 AM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Disconnected recordset as form source?
> 
> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>