bfuchs
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.
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.
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.
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.
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
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?
ASKER
Hi Scott,
Yes, all windows/office updates are installed, and this is happening to all pc's in our office
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.
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
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
ASKER
Hi,
This question is still relevant, if someone has an answer would be highly appreciated.
Thanks,
Ben
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.
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.
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.
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
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]
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.
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?
ASKER
Hi,
The table has about 250K records.
I see this only happens when displaying all records.
Thanks,
Ben
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
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:
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
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
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
ASKER
Hi,
I tried your bold suggestion and same problem.
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.
Thanks,
Ben
untitled.png
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 emptyYou 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.
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.
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
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.
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.
https://www.mediafire.com/?4hbf9g8kusl8s52
it has both exe and adp. Replace your msaccess.exe with mine, and run the adp.
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 reasonPerhaps because its an exe, try to rename as .ex and I will add the e.
Download fromSee attached error opening that site.
https://www.mediafire.com/?4hbf9g8kusl8s52
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...
www.vadimrapp.com/MSACCESS.rar
> hope this will not cause me problems elsewhere..
make backup of yours, of course...
ASKER
Tried with yours, same problem.
Thanks,
Ben
Thanks,
Ben
ASKER
Hi Vadim,
I will not be in the office tom, will resume next week.
Have a nice weekend.
Thanks,
Ben
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.
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
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?
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?
ASKER
Hi Vadim,
FYI- My SQL version is SQL Express 2005, this may be a factor in our different experience..
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
then in Profiler I see only one batchI'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 slowNo, see attached.
it opens fast, but repaints itself slow when you scroll it with mouse wheel, but fast if you scroll with the scrollbarThat'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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Vadim!
(And all experts involved)
"Knowing there is no answer - its also an answer"
Ben
(And all experts involved)
"Knowing there is no answer - its also an answer"
Ben
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
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.
ASKER
Hi Vadim,
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
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.
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.
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 thatDid 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.
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.
ASKER
Can be anything. Did you read the story about car engine vs. vanilla ice cream?I liked that!
Thanks,
Ben
Some issues occur when moving from one edition to another.