[AccessD] White screen

[Fred Hooper]

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