Access 2000 runs slowly

[dibby44]

Over a year ago, I created an Access 2000 database split front/back end, both on server and run on client pc's. It ran very fast when built, but now, over a year later, it has slowed a lot. It was designed according to Access guidelines, eg indexed fields, vb code not macros, etc. Some forms have linked documents, displayed as an icon, and my first thought is that the very large number of such documents now having been entered has slowed the db, even though they are only links as icons. Can anyone confirm that this scenario is likely?

 

[WayneRyan]

dibby,

I think the areas that you need to research are:

1) Get the front-end software on the User's computers. You don't want to
move the forms/reports around the net.

2) Look at the RecordSources for your forms. If they are based on tables,
then they will slow down as the tables grow. Look at basing them on queries
that restrict the amount of data that needs to be moved.

Wayne

 

[dibby44]

Have already tried moving front-ends to client pc and forms designed using selection queries - neither seems to improve the problem. Any thoughts on the expanding number of linked documents that I mentioned?

 

[GabrielR]

Have already tried moving front-ends to client pc and forms designed using selection queries - neither seems to improve the problem. Any thoughts on the expanding number of linked documents that I mentioned?

Not sure about that, but I would guess that as the Database grows those linked documents will have an impact on performance.

Compact and repair FE and BE. Compile all your code, and create a MDE so it will run compacted always.

 

[dibby44]

Thanks - I'll be seeing the customer on Friday 10th, so I'll be trying to resolve this then.

 

[The_Doc_Man]

As DBs grow, they get slow. Access is a small-system DB and uses small-computer paradigms. Eventually they get overwhelmed by the data load. Does your client ever archive old records out of the tables? Doing an archive and purge can regain lost performance. The way to handle data that could be current or in archived tables is to define a UNION query (look it up in the Help Files) to look at both the current and archived tables. For current or archived data but not both, don't use the UNION query.