Unanswered: Access Databases grinding to a halt - help please
I have written various databases for work here which have been added to daily for the last 5 years, they are now really starting to struggle. I use normal Access mdb front end, SQL2000 back end. 10 databases, the biggest of these is about 30 tables, largest table 50000 records. Each Front End has about 200+ queries, 50 forms, 200 reports. I tried before to rewrite all the databases as Access Projects but it was going to take months and I don't have the time, and not sure it would make that much difference to performance. Is there an alternative to Access Project using normal Access, without it opening all the ODBC connections whenever anyone opens the database?
Or is there some general things I can do to increase performance of these databases, the records probably double every 10 months or so, so if struggling now it's not going to last for long unless redesign.
Well the good news is that your indication of the size of the applications shouldn't inherently produce such degraded performance. The other good news is that there are probably many things that can be done to improve performance.
I also don't recommend migrating the back end to Access.
Here are just a few factors affecting performance.
1) Hardware/cpu/cpu speed
2) network hardware and architecture
3) Number of users
4) Usage patterns
5) Query complexity and design
6) Filtering of records
8) number of controls on forms and reports.
9) normalization issues
10) Access/SQL versions