Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

conditional formatting in access 2003 very slow

Hi Experts,
I have a continues form that has a text box bound to a date field and have the conditional formatting set if date is passed.
Now in Access 2000 that works fine, but when opening it with Access 2003 it loads very slow, actually it loads the screen twice, once for all other controls and then again for this formatted control, what is the solution/workaround for this?
p.s. I have installed office SP3 on all pc's.
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Try to recreate the form in 2003 and check.
Some issues occur when moving from one edition to another.
Also make sure the Windows install is fully up to date.

Does this happen on ALL machines, or just one or two?

Have you performed maintenance on the database? Make a backup, then do this:

1. Compact the database
2. Compile the database - from the VBA Editor, click Debug - Compile. Fix any errors, and continue doing this until the menu item is disabled.
3. Compact again

You may also need to Decompile. To do that, create a shortcut with a Target like this:

"full path to msaccess.exe" "full path to you db" /decompile

You can also do that with a Batch file, or directly from the Command line, or from the Run window.

After you've decompiled, go through the 3 steps above again.

Finally, you might consider creating a new, blank database and move everything there. If you do, then make sure to perform the 3 steps above again.
Avatar of bfuchs

ASKER

Hi Experts,

I just tried with a new ADP file, created new form and placed 2 controls in it, one with the conditional formatting, and the same problem happens, in addition I realized that the problem gets much worse if you select to format according to an expression, in that case it keeps reloading the form endless..

FYI- the back end is SQL Express 2005.

Any other suggestions?

Thanks,
Ben
Sounds more like troubles with your database, or with your installation of Access/Office or Windows. Are you 100% sure you're fully up to date with all patches/updates/fixes?
Avatar of bfuchs

ASKER

Hi Scott,

Yes, all windows/office updates are installed, and this is happening to all pc's in our office
Please post your sample adp here, together with the script to create the database objects the adp works with. We will verify.
Avatar of bfuchs

ASKER

Hi vadimrapp1,

Sorry for the delay, I am having an issue posting our app, as this would require approval from the manager..
however I just tried to create a new continuous form based on any table that has a date field, and setting up conditional formatting for "Field Value Less or equal to Date()", then when sorting the form desc on that field  you will see how it keeps spinning..

As mentioned, this is only happening in Access 2003, not in Access 2000.

Thanks,
Ben
Avatar of bfuchs

ASKER

Hi,

This question is still relevant, if someone has an answer would be highly appreciated.

Thanks,
Ben
Are you sure you can't post the adp? we are not asking for your real one, the sample one you created, with two fields, would be enough.

If you really can't, then please describe, the more details the better, what are those fields and what is conditional formatting specified for them. And how many records form's recordset has. We need to reproduce the problem.
Avatar of bfuchs

ASKER

Hi Vadim,

I created just small ADP with a test form linked to table below, displaying all records in continuous form and scrooling down will cause the problem to happen.

this is the script to create the table, I believe you only need ID and Day columns.
CREATE TABLE [dbo].[NCI_Calls](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CallID] [int] NOT NULL,
	[DateEntered] [datetime] NULL,
	[Initial] [varchar](5) NULL,
	[Day] [datetime] NULL,
	[Commission] [bit] NULL,
	[CallTime] [datetime] NULL,
	[FacilityID] [int] NULL,
	[Coordinator] [varchar](50) NULL,
	[CallersName] [varchar](100) NULL,
	[Request] [varchar](500) NULL,
	[FollowUp1] [varchar](1000) NULL,
	[FollowUp1Time] [datetime] NULL,
	[FollowUp2] [varchar](1000) NULL,
	[FollowUp2Time] [datetime] NULL,
	[NCNotes] [varchar](255) NULL,
	[OfficeNotes] [varchar](255) NULL,
	[Reviewed] [bit] NULL,
	[ReviewedBy] [varchar](50) NULL,
	[ReviewedTime] [varchar](50) NULL,
	[Imported] [bit] NULL,
	[DateImported] [datetime] NULL,
	[FollowUpEmps] [text] NULL,
	[RequestedDate] [datetime] NULL,
	[ts] [timestamp] NULL,
	[CommissionInitial] [varchar](5) NULL,
	[HomeCareInitial] [bit] NULL,
	[Notes] [varchar](1000) NULL,
	[ConditionalFormatColor] [varchar](50) NULL,
	[FollowUpEmpsTime] [datetime] NULL,
	[CallType] [varchar](50) NULL,
	[Prolucent] [int] NULL,
	[ProlucentFollowUp] [varchar](500) NULL,
	[IncomeProducing] [varchar](50) NULL,
	[Completed] [int] NULL,
	[PatientID] [int] NULL,
	[ProlucentFollowUpTime] [datetime] NULL,
 CONSTRAINT [PK_NCI_Calls] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window


See attached how the form looks like.

PS. let me know if you need the data and will see how to get it posted..

Thanks,
Ben
untitled.png
Untitled1.png
adpVadim.adp
> PS. let me know if you need the data and will see how to get it posted..

that would be ideal, but at least please tell how many records.
..and actually - find out if # of records has an effect. Such as, if the table has, say, 10 records, is it still slow?
Avatar of bfuchs

ASKER

Hi,

The table has about 250K records.

I see this only happens when displaying all records.

Thanks,
Ben
Make a copy of the table, and drop all but, say, 20 records. Will it change the issue?

Also, looking at your formatting condition, I'd certainly try the following: in your select, include this:

, case when [day] < getdate() then 1 else 0 end as Bold

and accordingly change the condition in formatting to "expression is", the expression being Bold=1
I tried your ADP, and the form opened fast, and scrolled also fast, no delay and no spinning. SQL Sever 2014 on local 8GB machine, Access 2003 (11.8321.8405), 200,000 records in your table populated by the following script:

declare @i int
select @i=1
while @i<200000 begin
  INSERT INTO [dbo].[NCI_Calls] (Day],callid)      select '3/17/2016',@i
  select @i=@i+2
end

select @i=2
while @i<200000 begin
  INSERT INTO [dbo].[NCI_Calls]           ([Day],callid)     select '8/1/2016',@i
  select @i=@i+2
end

Open in new window


Still, in real world, for 200K records I would certainly make the initial form empty (serverfilter = "1=0") and then populated it basing on some reasonable criteria - see the sample.
adpVadim.adp
Avatar of bfuchs

ASKER

Hi,

I tried your bold suggestion and same problem.

I tried your ADP, and the form opened fast, and scrolled also fast, no delay and no spinning.

Just to clarify, the problem is only when using the mouse wheel to scroll up/down records, not with the forms scroll bar.

In order for you to understand the problem take a look at the attached, see how its repainting/formatting record by record, while in 2000 I didnt have this issue.

Still, in real world, for 200K records I would certainly make the initial form empty
You right, I also use that approach in general, however this is a case where users needs to have access to the entire table, besides I remember having this issue with far less then 200K, its just that I cant remember where that was..

Thanks,
Ben
untitled.png
1. run Profiler on the server, see what's coming in. Should be only one select.
2. what is the your version of msaccess.exe? there was post-sp3 hotfix https://support.microsoft.com/en-us/kb/945674, but my version is even higher.
Avatar of bfuchs

ASKER

Hi Vadim,

Finally I was able to get the profiler ready for me (as now nobody uses, easy to find my stuff..).

See attached results.

Yes we do have the sp3 hotfix installed.

Thanks,
Ben
untitled.png
Untitled.png
Profiler trace - is that the same ADP that I uploaded? Is it the whole trace while the form was opening?

When I open the form in the adp that I uploaded, there's only 1 select. In yours there are 2. But the way you described it, paiting one record after another, looked like it might have been running it for every record. If there were only 2 times, it shouldn't be that big difference, but still why 2?

Try to replace msaccess.exe with this one, it's what I have. So we both run absolutely the same code.
can't upload for some reason. Download from

https://www.mediafire.com/?4hbf9g8kusl8s52

it has both exe and adp. Replace your msaccess.exe with mine, and run the adp.
Avatar of bfuchs

ASKER

Profiler trace - is that the same ADP that I uploaded? Is it the whole trace while the form was opening?
Yes, yes.
looked like it might have been running it for every record. If there were only 2 times, it shouldn't be that big difference, but still why 2?
also wondering why 2, however I dont think the problem is in SQL, rather in access formatting.
Try to replace msaccess.exe with this one,
hope this will not cause me problems elsewhere..
can't upload for some reason
Perhaps because its an exe, try to rename as .ex and I will add the e.
Download from

https://www.mediafire.com/?4hbf9g8kusl8s52
See attached error opening that site.

Thanks,
Ben
Untitled.png
try this:

www.vadimrapp.com/MSACCESS.rar

> hope this will not cause me problems elsewhere..
make backup of yours, of course...
Avatar of bfuchs

ASKER

Tried with yours, same problem.

Thanks,
Ben
Avatar of bfuchs

ASKER

Hi Vadim,

I will not be in the office tom, will resume next week.

Have a nice weekend.

Thanks,
Ben
The last 2 remaining pieces that is not the same, are the data in the table, and server location. Create the same almost-empty table as I did with the script above, and let's try with that. The number of read operations in your profiler was much more than in mine.
Avatar of bfuchs

ASKER

Hi Vadim,

OK I changed the SQL to only select top 20 records, (while that problem doesn't happen of course as with such small qty of records the painting process isn't noticeable), I was able to get the profiler, let me know if you see anything different.

Thanks,
Ben
Untitled.png
If I do the following:

1. open adp
2. open form
3. close form
4. start Profiler
5. open form


then in Profiler I see only one batch:

SQL:BatchStarting       SELECT NCI_Calls.*, CASE WHEN "day" < getdate() THEN 1 ELSE 0 END AS Bold FROM NCI_Calls ORDER BY CallID       
SQL:BatchCompleted       SELECT NCI_Calls.*, CASE WHEN "day" < getdate() THEN 1 ELSE 0 END AS Bold FROM NCI_Calls ORDER BY CallID       

The calls sp_MSHelpolumns are being made on the first opening, but not on subsequent ones. This is why steps 2-3 above.

What is the last batch in your trace - select ((0)) as columns ... ?

The bottom line is, so far we have not made our scenario the same. On that path, either you make yours the same as mine, or I make mine the same as yours. Probably the easiest first step is what I suggested in the previous comment: generate almost-empty table using the script I posted before, and try on that table.

Yet another variable is your computer itself. Is it powerful enough? Could it be that it takes this long time to calculate conditional formatting because its processor is slow? Mine is Intel i5 3.50GHz, 8GB RAM.

Finally: let's clarify once again, what exactly is slow. I just re-read this thread form the beginning, and it looks like in different times you mentioned different issues:

1.  opening it with Access 2003 it loads very slow, actually it loads the screen twice, once for all other controls and then again for this formatted control

2.  look at the attached, see how its repainting/formatting record by record, while in 2000 I didn't have this issue.

3. Just to clarify, the problem is only when using the mouse wheel to scroll up/down records, not with the forms scroll bar.

So: is it slow to open, or it opens fast, but repaints itself slow when you scroll it with mouse wheel, but fast if you scroll with the scrollbar?

If it's the latter, then I would question video driver. Can you try it on another computer?
Avatar of bfuchs

ASKER

Hi Vadim,

then in Profiler I see only one batch
I'm experiencing something interesting, some times it shows me two and some times only one like yours, not sure what it depends on..
What is the last batch in your trace - select ((0)) as columns ... ?
this time I didn't see that.

FYI- My SQL version is SQL Express 2005, this may be a factor in our different experience..

generate almost-empty table using the script I posted before, and try on that table.
Did that, however for some reason the form when opening is not showing things right..see attached.

Could it be that it takes this long time to calculate conditional formatting because its processor is slow
No, see attached.

it opens fast, but repaints itself slow when you scroll it with mouse wheel, but fast if you scroll with the scrollbar
That's the case.
then I would question video driver. Can you try it on another computer?
All pc's in our location have the same issue.

PS. its only when you first scroll to the middle of the records and then up/down with the mouse wheel where you encounter this issue.

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of Vadim Rapp
Vadim Rapp
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Thank you Vadim!

(And all experts involved)

"Knowing there is no answer - its also an answer"

Ben
Avatar of bfuchs

ASKER

BTW,

Here are the forgotten attachments-:)

If you have an explanation why the form looks like that there, I would be interested to know..

Thanks,
Ben
untitled.png
Untitled2.png
I have no good explanation of why the form looks like that, in fact I never saw anything like that and would say it's impossible if I did not see it on your screenshot: the height of the individual sections of your continuous form is visibly increasing from id 31 to 45, and vertical positioning of the textbox within the section is also different.  The only explanation I can imagine is a defect of the video driver. I would try it in Windows safe mode, just to see the result.
Avatar of bfuchs

ASKER

Hi Vadim,

The only explanation I can imagine is a defect of the video driver. I would try it in Windows safe mode, just to see the result.
The same.

Also tried in a different pc and got even worse, see attached.

PS. This only happens when opening the form direct from the database container, however if opening first in design mode and switching to form view then is fine.

Thanks,
Ben
Untitled.png
If I see correctly, the background is showing through the form, is that right? so the space between the rows is kinda transparent.

It's certainly something way beyond Access, something along the lines of video driver, Windows video effects and such. To find out what it is, the only way is to eliminate effects one by one, such as try on clean virtual machine, try with different resolution and so on. The light at the end of tunnel is my statement that on my system I don't see anything like that, so there must be something unique with yours.
Avatar of bfuchs

ASKER

It's certainly something way beyond Access, something along the lines of video driver, Windows video effects and such.
In that case why would I see it in both machines tested with?

I don't see anything like that
Did you also tried opening directly from database by double clicking on icon?

PS. This is actually something not related to the original question, dont feel obligated to reply..-:)

 (if this turns out complicated I may open another post)

Thanks,
Ben
> In that case why would I see it in both machines tested with?

No idea. Using Sherlock Holmes' method of deduction, it means that your machines have something in common, but not with mine. Do they have the same video driver? As bizarre as it sounds, are there any strong electromagnetic fields near your place? Nuclear reactor perhaps? Can be anything. Did you read the story about car engine vs. vanilla ice cream?

> Did you also try opening directly from database by double clicking on icon?

Tried now, no change. All rows are equal, nothing shows through.

In any case, what's on your screenshot is certainly an anomaly that can't have some logical documented explanation. The question is where - in Access, or in video driver, or in Windows. Accordingly, the only way to isolate it is to exclude ("deduct") one suspect after another, until the effect disappears.
Avatar of bfuchs

ASKER

Can be anything. Did you read the story about car engine vs. vanilla ice cream?
I liked that!

Thanks,
Ben