Unanswered: Form/report performance and database permissions
I am currently having performance problems with a large Access 2000 database. It is the frontend that is the problem, it is around 300MB in size with 26 linked tables, around 260 queries, 160 forms and 80 reports. The problem is that certain forms and reports will take upwards of 10 minutes to load. I initially thought that this was down to the large amount of data, but then I found if I run the underlying query the data will appear in a few seconds.
After further experimenting, I found that if I opened such a form or report in design view, made a minor change (just the header or something), saved it, and opened it up again, it would take a few seconds. Then over time the performance would continue to drop off.
Also, if I do a repair/compact, certain forms and reports will again take a very long time to open.
After reading through this board, I decided to try importing all the objects into a new blank database to see if this helped. And it certainly did, with all forms and reports loading up in seconds. However the problem is the user and group permissions were not also imported. This is a big problem because with so many objects, and over 30 users, it would take a huge amount of time to reassign all those permissions. If it was a one off it might be possible, but if I have to keep doing this to keep the performace up, it just would not be possible.
So my questions are: Is there a simple way to copy over the user and group permissions? Failing that, is there anything else anyone can think of to improve the performace of the original database?
I cannot offer any advice on the security probs, but I do want to offer empathy on your troubles with the performance.
I too have similar problems. We have over 10 databases here and all are now slowing to a painfull point. It's so bad infact that some staff with slower PC's cannot use them at all.
The worst database is fully normalised. It compiles itself on opening (this takes about 2 mins but no-one minds as it's only one a day) and although being code heavy, is no where near as VB dependant as it could be. It is also split into a front and back end.
Does anyone know why this is? I've spoken to our network people and they tell me that it's nothing to do with that.
In order to give some specific recommendations, more information is needed. What version of Access are you using? How many users do you have? What is the average number of transactions in a day? What kind of a network, what kind of a machine does the database reside on? Is it a split database? What kind of record locking are you using? Are you using data-bound forms? Have you analyzed the indexes on your tables? Access performance in a multi-user environment is pretty complex, but a good resource is