[AccessD] White screen

[Bill Benson]

Fred it's far past a day and I am anxious awaiting the next installment. ..
how is it going?
On Apr 27, 2016 8:12 PM, "Fred Hooper" <fahooper at gmail.com> wrote:

> Weird.
>
> When I ran the code that the view runs in Management studio it executed
> the code once, taking 982 miliseconds and showed the code that I ran.
>
> When I selected * from the view in MS it executed it once, taking 997
> miliseconds.
>
> When I double-clicked the "table" linking to the user view, it first ran
> the selection from the view, taking 144 miliseconds. Then, it ran
> sp_execute six times for a bit more than 800 ms each, for a total of
> about 4850 ms.
>
> When, I allowed the Requerey to execute it ran more than 400 lines in
> the profiler, each just under a second. However, because I'd run from
> the "table" above, the form wasn't maximized. When I clicked it to
> maximize it ran another 230 lines, at the same pace but pausing
> occasionally. Then, I hit the [Close] button and it ran another 22 lines
> at 1 second each before it closed.
>
> Finally, when I ran it normally, it ran 480 lines in the profiler at
> about a second each -- reasonably close to my 10 minute estimate.
>
> It's clear that you've put your finger on the problem. Thank you.
>
> It seemed such an elegant solution, it's too bad it doesn't work. Do you
> have any idea what's going on?
>
> I'll run the profiler with simple link structures tomorrow and post the
> results.
>
> Thanks again,
> Fred
> > Stuart McLachlan <mailto:stuart at lexacorp.com.pg>
> > Wednesday, April 27, 2016 5:47 PM
> > Do you have parent/child links between the form and subform?
> >
> > Where is your Form_Subform.requery triggered, what exactly is the code
> > that does this?
> >
> > >From your description. the requery is being called repeatedly - you
> > should be able to monitor
> > the connection in SQL Serv Management Studio to confirm this.
> >
> > What happens if you build a simple query to a single temporary local
> > table and try exactly the
> > same process with that?
> >
> >
> >
> > ...
> >
> > Fred Hooper <mailto:fahooper at gmail.com>
> > Wednesday, April 27, 2016 12:27 PM
> > Hi All,
> >
> > I'm converting someone else's Access 2002 program to a SQL Server 14
> > back end. I apologize in advance for the amount of information, but I
> > didn't want to waste your time suggesting something that I've already
> > tried. And, since I've been unable to find much help on the web,
> > apparently my problem is unusual.
> >
> > Background:
> > When I asked a question about converting to SQL server relating to
> > dbSync a few weeks ago I received two suggestions:
> >
> >  1. Improve the network speed. I did that but it made only a small
> >     difference.
> >  2. Just replace the links to the Access back end with links to SQL
> >     server. This didn't work; too slow.
> >      1. Most of the queries that feed the forms are complex; 8-15
> >         tables, mostly outer joins and many fields (so the same query
> >         can be used for different purposes). It took upwards of 20
> >         minutes to populate a form with this approach.
> >      2. I moved most of the queries to SQL Server and linked to them.
> >         This reduced the time to populate to 10 minute, still
> >         unacceptable.
> >  3. BTW, all of the queries, direct and indirect, populate much more
> >     quickly from the database window; it's just populating through the
> >     forms that is causing me problems.
> >
> > So I moved to a more complex approach:
> >
> >   * Non-editable forms are populated by ADO recordsets. This is quick
> >     and works fine.
> >   * Editable forms are the problem
> >       o Because the back end doesn't enforce referential integrity
> >         (reference link
> >         <
> http://rogersaccessblog.blogspot.com/2009/11/this-recordset-is-not-updateable-why.html
> >)
> >         I can't update through the complex queries in the recordsets.
> >       o So I'm trying "user views": Views named for the pc/user
> >         concatenation (because users can be logged in on multiple
> >         pc's). For example:
> >           + A view named "QryActionsReport_2_PUGET-88049" contains the
> >             SQL code "SELECT * from dbo.qryActionsReport WHERE
> >             charindex('jones',[cusCompanyName]) > 0". This returns 80
> >             records.
> >           + This query replaces the QryActionsReport query in the
> >             database window, but the local name doesn't change. A
> >             proper unique index is applied.
> >           + In the database window the query populates in 4-5 seconds
> >             and is editable.
> >           + This process effectively creates an editable passthru query
> >       o The main form has no data source. There's a single subform
> >         with QryActionsReport as its RecordSource. The user view when
> >         the form is opened has "where 1=0", and populates instantly.
> >           + The code changes the user view to look for 'jones' and the
> >             local query link is refreshed.
> >           + I repopulate the subform with "Form_SubformName.Requery"
> >             and my problem begins:
> >               # It seems to take forever, actually just 10 minutes.
> >               # On the subform 6 lines are slowly populated, 2 seconds
> >                 per line. Then, the subform blanks, and it slowly
> >                 repeats 22(!) times
> >               # Interestingly, code that executes after the Requery to
> >                 populate another field is executed immediately after I
> >                 press the button to populate the subform, while the
> >                 first record on the subform is slowly filling.
> >               # After the subform seems populated it still isn't
> >                 ready. If I click on the main form a couple of times
> >                 the whole form gets a whitish overlay (the "white
> >                 screen" in my subject) and I can no longer get to the
> >                 open code window.
> >               # After the form can be used and I close it the form is
> >                 no longer maximized and the subform begins to populate
> >                 again, filling 6 lines slowly, before it finally closes.
> >   * Things I've tried that haven't worked
> >       o Changing the link to the user view between ODBC and DSN-less.
> >         (I'm using ODBC 11, the native connection to SQL Server 12 and
> >         14.)
> >       o Temporarily used Debug.Print in the subform Current event, but
> >         it fires only once and does so immediately. There's no Current
> >         event in the main form as it has no data.
> >       o As the program uses only maximized forms, I put a check for
> >         already-maximized in the main form's Activate event.
> >       o Installed the most recent video driver
> >       o Changed the display driver from generic to the specific display
> >       o Changed from a development version of SQL Server 2012 to SQL
> >         Server 2014 Express
> >   * Things I've thought of trying, but haven't yet
> >       o Establish referential integrity in the back end
> >       o Buy a video card to replace the motherboard-included Intel
> >         3000 display "card"
> >
> > My PC is 3 years old with all programs and operating system (Win 7
> > pro) up-to-date. All code and data is operating off a solid-state hard
> > drive.
> >
> > I really appreciate your reading so much and I hope that you can help me.
> >
> > Best regards,
> > Fred Hooper
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>