[AccessD] White screen

[Fred Hooper]

Hi Dan,

I'm not getting paid; it's for my brother-in-law's company and I
volunteered. I'm easing towards retirement -- only one paying client
left, with declining volume -- and enjoy doing something like this. The
first line on my resume for decades has been "I enjoy working on
complicated things with pleasant people." This meets both criteria.

1) I'm sure you're right, but my problem isn't query performance (which
is ok), but form+query performance. Opening my example query in the
Access database through the link to SQL Server takes 4 seconds, opening
the form that uses that same query as its RecordSource takes 10 minutes.
I want to fix the 10 minutes before I work to reduce the 4 seconds.

2) I'm trying to make as few code changes -- versus additions -- as
possible, so I'm avoiding rewriting the queries. This also appears to
relate to my answer to your 1).

3) SQL's charindex is similar to Access's Instr (with reversed syntax),
so I'm not using an index value as a criteria. I began using it as a
replacement for "Substring" and "Like" after reading this
<http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex>,
which showed charindex superior for all instances when querying fields.

Thanks,
Fred

> Dan Waters <mailto:df.waters at outlook.com>
> Wednesday, April 27, 2016 2:41 PM
> Hi Fred,
>
> Hope you're getting paid for this!
>
> Three things I can think of ...
>
> 1) Look at the tables to see how many indexes exist. (I'll assume that all
> your tables have a Primary key which is not concatenated.) For me, I only
> use a single Primary key which is the same as the row identifier. To
> require another index actually requires a lot of testing to show that
> another index will help pulling up a dataset. If you have many indexes
> on a
> table then try removing them to see how your performance changes.
>
> 2) I typically use a 'stacking' of simple queries rather than a single
> complex query, especially when any 'joins' are involved. I've seen where
> this can dramatically reduce the time it takes to display a dataset.
>
> 3) I was also surprised to see a query that used an index value as a
> criteria. Try writing that query simply as:
> SELECT * FROM qryActionsReport WHERE Name = 'jones' AND
> cusCustomerName = 'strCustomerName'
> after you have removed all the indexes. Perhaps that would be faster.
>
> Good Luck!
> Dan
>
>
> 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