Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

conditional formatting in access 2003 very slow

Posted on 2014-09-01
39
Medium Priority
?
309 Views
Last Modified: 2016-06-08
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.
0
Comment
Question by:bfuchs
  • 18
  • 16
  • 2
  • +1
37 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 40297447
Try to recreate the form in 2003 and check.
Some issues occur when moving from one edition to another.
0
 
LVL 85
ID: 40298045
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
 
LVL 4

Author Comment

by:bfuchs
ID: 40299567
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 85
ID: 40299700
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
 
LVL 4

Author Comment

by:bfuchs
ID: 40299721
Hi Scott,

Yes, all windows/office updates are installed, and this is happening to all pc's in our office
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40311947
Please post your sample adp here, together with the script to create the database objects the adp works with. We will verify.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40327196
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41627554
Hi,

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

Thanks,
Ben
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41627589
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41629415
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41629484
> 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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41629486
..and actually - find out if # of records has an effect. Such as, if the table has, say, 10 records, is it still slow?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41629498
Hi,

The table has about 250K records.

I see this only happens when displaying all records.

Thanks,
Ben
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41629599
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41629726
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41629773
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41629805
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41632109
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41632211
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41632225
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41632236
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41632274
try this:

www.vadimrapp.com/MSACCESS.rar

> hope this will not cause me problems elsewhere..
make backup of yours, of course...
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41632332
Tried with yours, same problem.

Thanks,
Ben
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41632351
Hi Vadim,

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

Have a nice weekend.

Thanks,
Ben
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41633614
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41637820
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41637925
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41639682
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
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 2000 total points
ID: 41639801
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
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 41639979
Thank you Vadim!

(And all experts involved)

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

Ben
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41639982
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41640038
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41642495
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41642630
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
 
LVL 4

Author Comment

by:bfuchs
ID: 41642702
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 41642777
> 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
 
LVL 4

Author Comment

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

Thanks,
Ben
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question