Storing and sharing database on slow server

[kagoodwin13]

I am trying to find the best method of sharing my database now that it has been completed. I am running into a problem though: the common shared drive between my users is prohibitively slow. Even after compacting and repairing the database, and splitting the backend to the shared drive, it still takes 30 - 60 secs to load a form or report. I do not think my database is the cause of this because the same operations take mere milliseconds on local storage.

Right now I see my options as:

1. Give everyone a copy to store on their local machine (with no update capabilities)
2. Park the database on a shared drive (very slow)
3. Give everyone a copy of the split front end (still slow but faster than option 2, and no front-end update capabilities)



I do not have the option of updating the drive or network because I do not work in IT. Does anyone have any tips on how to optimize a database for use on a slow server or shared drive?

[Bob Fitz]

In a multi user db each user must have their own front end which is connected to a single back end.
If forms are slow to open you could try this:
Remove the Record Source of each form and set it in the forms On Load or On Open Event. I have done this in the past but I can't remember if it matters which of the two events you use.

[kagoodwin13]

Thanks for the fast reply.

My record sources for my forms are queries, because each form is an empty search form with unbound fields. The query takes criteria from a form and generates a report with search results. Is there a way to make this work in the way you described?

Also, I tried what seemed to be a common tip: upon load, I now have a hidden form that links to a table with one record. This seemed to speed up loading forms/queries/reports, however it's still a bit slow (10-15 sec load). Any other tips would be greatly appreciated.

[Bob Fitz]

If a form has its Record Source property set to the name of a query (say qryCus), then remove this setting and set it in the forms On Load event with something like this:

Private Sub Form_Load()
  Me.RecordSource = "qryCus"
End Sub

[kagoodwin13]

This worked but did not seem to improve speed, and seemed to decrease performance when loading a report.

For my own reference, I saved an Excel file with one character in the same location. It takes roughly the same time (9 seconds) for the file to open as it does for one of my forms to load.

I may just be bottlenecked by this shared drive.

Thanks again for your help.

[Bob Fitz]

Well although 9 seconds is better than it was, if the forms have combo boxes or list boxes, I believe performance could be improved by setting their "Row Source" properties when the form opens as well. Take a look at: http://www.granite.ab.ca/access/performanceforms.htm