long pauses going through records in form

[malum]

Sorry if this has been covered previously

Access 2003 with an Access 200 db

If I run through the records in form view either by holding the mouse button down on the next arrow or by clicking it quickly it will stop at certain records (the same ones) for a long time before displaying these records

It's on a network but copying it to the local hard drive didn't help

On one of the machines here (the newest and most powerful 3Ghz P4) it is really bad pausing for a very long time on many records. On another less powerful machine it is nowhere near as bad. I only noticed it because the more powerful machine was only installed today. I have previously not had this problem at all.
I have tried compact and repair and converting to access 2003 format but it makes no difference.

anyone have any thoughts

TIA

 

[The_Doc_Man]

Questions that come to mind:

1. Does the form have a lot of code "behind the scenes" - such as Form_OnCurrent events? If so, look at the events.

2. Does the form have any fields that are computed by evaluating a counting query of some sort? A DCount or DMax or DMin would do it. If so, look at the number of things being counted for the fields that hesitate.

3. Does the form have a subform based on tables with a one-to-many relationship?

4. Is the form based directly on the table or on a query? Does either the form or the underlying query have filtration criteria?

5. Does the underlying record (Query OR Table) have a really big Memo field? And for the hesitating records, is that Memo field non-trivial?

6. Is there a "default value" setup in one of the fields such that you would automatically update a record just by trying to leave it? (Most common case of these is a date field showing date/time of last "touch".) If so, look at how many indexes exist on the underlying table.

7. Does it reference any externally linked tables?

8. Look at the amount of Swap File space on each machine to see if that correlates with the problem. One issue that often comes up is that when Access traverses a recordset, it consumes virtual memory. At some point, it consumes enough that it has to swap out and back in again. (Has to do with the mechanics of updating the memory management tables.) At that point, if the Swap Files are badly fragmented (more likely to occur on smaller Swap Files), it takes longer to do the OutSwap/InSwap operation.

9. You can test whether it is a resource problem by finding your Windows System Performance utility, usually from the Start >> Accessories >> System Tools menu path. Here's how to use it.

Start Access. Open the form in question. Now minimize Access (don't close it, though.) Start the resource monitor. Look at the resources. OK, restore the Access window. Try to resize everything so that you can somehow get the performance tool in one window pane and your Access form in another pane. I.e. both panes visible. Now start traversing records. Watch for spikes in the CPU time and for spikes in resource usage. If you see what resource spikes, you know you have a resource problem. If not, you have some other problem.

That's all I can think of while shooting from the hip.

 

[malum]

The answers to 1-7 are No (well the answer to 4 is it's a table)

I will check out the resource situation tomorrow and report back

Thanks for the input

 

[The_Doc_Man]

No problem. Good luck.

If none of the suggestions are helpful, you'll have to hope someone else has seen this 'cause beyond what I suggested, I'm drawin' blanks.

 

[malum]

It's using next to no extra paging file (certainly no big spike) and the biggest cpu usage was 23%.
It's very odd. It never used to do it. I'll keep plugging away

 

[The_Doc_Man]

I have tried compact and repair and converting to access 2003 format but it makes no difference.

Actually, it DOES make a difference. By doing those tests, you have eliminated file and version artifacts from the equation. It is a GOOD debugging action. Helps narrow the focus.

holding the mouse button down on the next arrow or by clicking it quickly it will stop at certain records (the same ones) for a long time before displaying these records

I reiterate: Particularly with respect to the fact that you have tested version conversion as well as repair and compaction, something funky is going on with those specific records. So you will have to focus on the records themselves or the things the form does for those records.

If the records include OLE objects, hyperlinks, or large chunks of binary (like a memo field subverted to hold a binary large object, or BLOB): See if any of the objects are displayed or expanded on the form. For OLE objects and BLOBs, see if a shrink or expand has to occur to expand the item. For hyperlinks, look at the object being referenced to see if it is unusually complex to expand.

If hyperlinks or OLE to a separate file appear (and are expanded) on the form: I would look at the pathing that leads to the external item because folder traversal can be a bear sometimes. On a new vs. old system, this might conceivably change. I would also look at how many Access Control Entries are on each traversed folder. (Looking for large numbers of folders to traverse and large number of ACEs to evaluate.)

If the form includes a PICTURE: I would examine the tiling, scaling, and stretching attributes chosen for the corresponding image control. See if one of the attributes includes ROTATING the image in those cases that hesitate.

Now, THIS is a long shot indeed, but ...

On one of the machines here (the newest and most powerful 3Ghz P4) it is really bad pausing for a very long time on many records. On another less powerful machine it is nowhere near as bad.

If a file is involved with each record, determine its type. Find out what program opens that file type. Make an intelligent guess if necessary. You can see the file types opened with each program using:

MyComputer (double-click to open) >> View >> Options, select tab for "File Types"

Scroll through the file types until you find what it is that opens that particular file. See if the new machine and the old machine use the same processor for that file type. If not, you might have found your culprit.

For instance, on my HP box at home, a JPG opens with the HP Image Zone software, but on my wife's older COMPAQ Presario box, it opens with Windows Image Viewer. Two VERY different programs. I'm not saying this is your exact problem, but it might be something analogous.

 

[malum]

many thanks

I will look into all those options as soon as I get a chance. I will report back my findings