[AccessD] White screen
Dan Waters
df.waters at outlook.com
Wed Apr 27 13:41:14 CDT 2016
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
-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Fred Hooper
Sent: Wednesday, April 27, 2016 11:27 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] White screen
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-updateab
le-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
More information about the AccessD
mailing list