[AccessD] White screen

[Fred Hooper]

There are no links between the main form and subform; the main form has
no data.

When the user pressed a [Select] button, inputs in the main form are
used to create a Where clause. With this, the SQL statement is
constructed and converted to SQL Server SQL. This code is then used to
create a "user view", which is  then substituted for the source of the
original QryActionsReport and the link refreshed. Finally, after all
this is completed the Requery is done.

Yes, it does appear so. I'll learn how to monitor that connection, find
out if it's doing so, and let you know.

In the past, I tried code that populated a local table from the server.
I abandoned that approach because of the difficulties of updating back
to the server. But the form worked properly under that approach -- as it
does when I switch everything back to the Access back end.

Thanks, I'll let you know the results of your third question when I find it.

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