roz.clarke at barclays.com
roz.clarke at barclays.com
Sun Mar 25 05:47:06 CDT 2012
Thanks Charlotte! I'll go through these. :) -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: 25 March 2012 00:54 To: Access Developers discussion and problem solving Subject: Re: [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=f27 a794a-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 > > > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com