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.
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
Try to recreate the form in 2003 and check.
Some issues occur when moving from one edition to another.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
bfuchsAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
bfuchsAuthor Commented:
Hi Scott,

Yes, all windows/office updates are installed, and this is happening to all pc's in our office
0
Vadim RappCommented:
Please post your sample adp here, together with the script to create the database objects the adp works with. We will verify.
0
bfuchsAuthor Commented:
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
0
bfuchsAuthor Commented:
Hi,

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

Thanks,
Ben
0
Vadim RappCommented:
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.
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
> 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.
0
Vadim RappCommented:
..and actually - find out if # of records has an effect. Such as, if the table has, say, 10 records, is it still slow?
0
bfuchsAuthor Commented:
Hi,

The table has about 250K records.

I see this only happens when displaying all records.

Thanks,
Ben
0
Vadim RappCommented:
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
0
Vadim RappCommented:
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
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
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.
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
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.
0
Vadim RappCommented:
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.
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
try this:

www.vadimrapp.com/MSACCESS.rar

> hope this will not cause me problems elsewhere..
make backup of yours, of course...
0
bfuchsAuthor Commented:
Tried with yours, same problem.

Thanks,
Ben
0
bfuchsAuthor Commented:
Hi Vadim,

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

Have a nice weekend.

Thanks,
Ben
0
Vadim RappCommented:
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.
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
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?
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
Ben. it looks like you forgot to attach whatever you planned to.
---------

LOL. Your PS is really important! I now tried it, and although I wouldn't say it was really slow, I did see some painting, and the farther from the beginning I scrolled, the slower it went. I imagine, with the real data in the table it might be worse.

It's almost certainly the issue of Access programming, most likely related to how Access decides where it is at among the records, and accordingly which ones to format. You can see that when you scroll, while you still hold the mouse button depressed, you can see new records coming into the window, but they are not formatted yet - because Access does not know yet if it's the "final destination" or not. But once you have released mouse button, it repaints the records on the screen, now already with formatting. I think, this is what you meant by double-painting. It's not really double, it's that Access shows you "a preview" while you scroll, and shows "the full picture" once "you have arrived". Why this process slows down with increasing the distance from the top - because that's how this particular piece of Access 2003 is programmed.

Apparently, in Access 2000 this piece of code was programmed differently. How and why, only Access programmers can tell. Maybe new method has ensured some other benefits. I doubt there would be coherent answer from Microsoft even if Access 2003 was still supported, and even if you opened paid support indicent. You can still try it  (I just did : result: "This product may not be eligible for all online support options. To create a support incident call Microsoft." → https://support.microsoft.com/en-us/gp/commercialsupport). Since this is undocumented issue, you almost certianly won't pay a dime in the end, but as I said, given that ADP's are deprecated, I doubt there would be any real outcome, aside from purely academic interest. There might be more hope if you reproduce it in a modern version of Access, working with the same SQL Server via ODBC, or maybe even in the mdb (or actually, in accdb)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thank you Vadim!

(And all experts involved)

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

Ben
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
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.
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
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.
0
bfuchsAuthor Commented:
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
0
Vadim RappCommented:
> 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.
0
bfuchsAuthor Commented:
Can be anything. Did you read the story about car engine vs. vanilla ice cream?
I liked that!

Thanks,
Ben
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.