[AccessD] White screen

Fred Hooper fahooper at gmail.com
Thu Apr 28 15:32:30 CDT 2016


I tried the most recent ODBC and Native Clients for SQL Server as DSN
links to SQL Server 2014. The results were depressingly similar to
yesterday's below.

Again, Stuart, thank you very much for identifying my problem.

If anyone has a solution for the problem I'd appreciate learning it.

At this point my only viable alternative seems to implement relational
integrity in the back end -- clearly a good thing to do, anyhow -- so
that I can use ADO recordsets to feed the forms and be able to edit the
data.

Thanks,
Fred
> Fred Hooper <mailto:fahooper at gmail.com>
> Wednesday, April 27, 2016 8:10 PM
> 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


More information about the AccessD mailing list