Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    15

    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.
    thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Aberdeen, Scotland, UK
    Posts
    168
    I've never actually used Access with Sql as a back end but I may have an idea(then again it may be a stupid idea).

    Can you import all the corresponding tables straight to Access without changing the names, and therefore access not knowing the difference.

    However, Sql can handle WAY more data than Access (in tables) so I wouldn't recomend changing your databases to be just Access.

  3. #3
    Join Date
    Jan 2003
    Posts
    46
    MrC,

    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
    7) indexing
    8) number of controls on forms and reports.
    9) normalization issues
    10) Access/SQL versions
    etc.


    Cheers,
    zambezibill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •