Access continuous form takes 100% cpu

I have a problem with an Access application that we have.  It runs fine on our XP PC's but has a problem with a few continuous forms, when run on our Windows Server 2003.  We are using Access 2003.  I am concentrating on one single form - this happens with several similar forms.

The problem is that the listing (of journeys) uses a query that links the journeys table with the driver and passenger tables.  When the form is opened it displays the data from the journey table, but the data from the driver and passenger tables is blank and the server cpu is up around 80 - 100%.  If left for a few minutes it will eventually display.
I repeat that this only happens on the 2003 server.  In all cases the backend is running on a second server (our main file and domain server).

The tables are not significantly larger than they have been in the past.

I have taken the liberty of attaching a cut down database and backend (no sensitive information).

I would welcome any suggestions as to how this problem can be diagnosed and fixed.

Best regards

Richard Tomalin
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.

Jeffrey CoachmanMIS LiasonCommented:
Are you employing an indexing strategy on these tables?

If you run the query on its own, how long does it take?

Anything complex with the form? (Images, calculated controls, VBA code...etc)
rltomalinAuthor Commented:
There's nothing really complicated.  If I run the query on it's own (even on the server) it returns instantly.

I can't see anything complex that would slow it down (I have attached the db so folks can look at it).  And anyway, it works fine on an XP PC so it seems like it could be a server issue.  I have re-installed Office on the server but it made no difference.

You can check the indexes on the tables - I think they are just a simple index on the Passenger/Driver ID numbers.


Jeffrey CoachmanMIS LiasonCommented:
<I have attached the db so folks can look at it>
It did not attach...
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

rltomalinAuthor Commented:
Hello Boag2000
It's there.  I attached it when I compiled the original question.


Jeffrey CoachmanMIS LiasonCommented:

Ok I see it in your initial post...

Is the number of records in the BE representative of the actual number?

Then only thing I can see is the calculated fields in the form (especially the nested IIF)
Try putting these calculations in the forms recordsource and see if that makes any difference.

If you create a "test" copy and combine the FE and BE, and put them both on the server, what the performance like?

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
rltomalinAuthor Commented:
Yes the BE is the actual size in terms of records.
I will try the things that you suggest - thanks


Jeffrey CoachmanMIS LiasonCommented:
Then put the "Combined" DB on your local machine and test...
rltomalinAuthor Commented:
Hi Boag2000
Thanks for the suggestions.  I have moved the calculated fields into the query and that seems to have solved the problem.
I still do not understand why (a) the problem only occurs on our server and (b) why similar forms with the same calculated fields work fine.
But life's too short to worry too much about that - it all seems to be working fine now.
Thanks for your help.


Jeffrey CoachmanMIS LiasonCommented:
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 Applications

From novice to tech pro — start learning today.