Performance Drop from 97 to 2k

[Carnafex]

I was wondering if anyone else had a large performance drop in their databases when upgrading from Access 97 to 2000.
The problem with mine is that I have a lot of code and stuff running in a particular form (it gathers an employee's details) and because there was quite a bit of information to gather, it slows down a bit. I know it runs a little slow anyway because I have a lot of code and also the database is split into a Frontend and Backend.

So in Access97, the form ran slow when changing records, but was still useable ( it was about a 3 second delay). Now that I just upgraded to Access2000, the delay now goes to about 15 seconds! This I consider totally unusable.

So does anyone out there know why this would occur? I've already tried to compact the database, and I am experimenting with different VB code that might be faster. We also just upgraded the network to base100 (as opposed to base10) LAN which should speed up the front/back end connection no end, but it still runs slow.
Any help would be appreciated.

Thanks
Jason

 

[Mile-O]

Does this thread help?

I found that my databases went really slow since converting to Access 2002 from Access 97. I left my backend as Access 97 and it runs with no noticeable delay now.

 

[Carnafex]

Thanks for the quick reply Mile-O-Phile.

I read the thread you posted and unfortunately, my Backend is already Access97. However, I just tried something else that sped up the speed considerably.

I have an attendance record that is calculated on the fly from a subform and displayed on the front page. The summary simply calculates the 'sick' days the person has had over the last month, 3 months, and last year. It does this 5 times for different definitions of a sick day (sick/late/notified etc) and then uses this information to calculate the percentage breakdown of weekdays that the person was sick (monday seems very popular...hmm )

By disabling this large piece of VB code, the speed of the form goes back to what it was before (meaning 97 ran about 5 times faster in my database than 2k for the same code). I have a feeling that this is due to the 'brute force' programming methods I used to make it. (I had just started VB back then)
From above, you can see there were 3 time periods, 5 different definitions of a sick day, and 5 weekdays (Mon -> Fri) meaning there are 30 calculations in all. Each calculation however uses a Domain aggregate function (DCount), and I know DCount is slow, but 2k seems to handle it differently.

Now is there a way to change it so it doesn't use DCount? I could use a query, but it would be large and messy (many conditions have to be met), and I really dont know any other methods to increase its speed.

Cyas
Jason

 

[Keith P]

I have found on occasion using 2k that a form can take an age to load. Through trial and error I discovered that if the form had a date field with the default value set to Date() it could take an age. Remove the default value and the response was instant. I can’t explain why because other forms with the default date setting work fine.