If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: help needed on database speed when splitting tables

Hi guys,

I have split my database and put tables on a shared drive at work. How ever it has made the whole application slow, i have used the optimise function in acces to help with speed.

The front end is located on the local Hard drive the Backend is on a shared drive. however it still takes forever to open forms etc. I thought the forms are held on the front end and should not take long to load.

Im talking a minute a form. Is there anyway of helping me make this quicker?

Do you have subforms? You may want to see the speed tips in the code bank. Loading ALL records on a form with 1 or more subforms tends to impact speed (especially when splitting on a possible slow network). Along with comboboxes, multiple listboxes, complex equations, dlookups, etc....

But like ST mentioned - it could be lots of other things. See Page 5 or 6 (speed tips) in the code bank.

The source of your whole problem could also simply be that user's don't have read/write permissions to your shared folder (where the backend mdb resides). I'd try checking that first.

Othewise, you don't need to make any major changes. You just need to do a few things on when to populate the "sourceobject" of the subform.

Keep in mind if you load "ALL" the records on the main form, you're loading these for the subforms also. That's why setting criteria on the mainform so it only returns 1 record (for large recordsets) will help with your form speed. Slowness of your forms opening and your design of them is not as prominent until you separate the tables into another mdb and put that backend in another location such as over a network type connection. You may also want to try putting the frontend on the shared drive with the backend (and relinking the tables). I try to keep the frontend and backend together as it makes it easy for code changes. If this doesn't help (and there's no other network-type issues for users and it's not a daisy-chain hubbed network), you really need to start looking at how you load recordsets in the subforms (ie. sourceobject.) (Note: Daisy-chain hubbed networks can dramatically decrease connection to an mdb file.)

First try this...

Design a new simple MainForm (with no subforms/comboboxes/listboxes) based on 1 table only (rule of thumb (usually): 1 table per form/subform if you're updating on that form/subform!!). Use criteria for your new Mainform to open 1 record only. If it's still slow opening that MainForm, you'll now need to look at why you're connection is slow to the backend tables.

Then Try this...

Make a backup of your mdb. Then in the backup, try this...
On your mainform (design mode), remove the "sourceobject" property for all your the subforms (don't forget any within a tab control - you don't need to delete the subform, just remove the sourceobject so it will be blank when you open the mainform.) (but keep your MainForm bound to the same recordset).
Open your mainform and see if it loads faster.
If it does, you know that your subforms are a factor and you should set the "sourceobject" of certain subforms (especially ones not initially visible on the mainform) to blank and then populate the sourceobject of that subform in code after a specific event (ie. onChange event for a tab or when the button is clicked to show the subform.)
If it doesn't, try setting the recordsource of your MainForm with criteria so it only opens 1 record. If the Main form loads faster now, you know you'll need to use criteria to open that Main form faster.
If it still loads slow even with criteria to open just 1 record, then remove the rowsource property for all your listboxes/comboboxes and try again. If you have a lot of comboboxes, you may need to consider changing these to text boxes (if possible). (Again, we're trying to test to see our limitations with the current network compared to the design of the form.)

...

You can also "load" your subform's on certain events. Tabs are big culprits as they usually have a "bound" subform sourceobject in each tab.

Instead, I'll set the the "sourceobject" of a subform when the user clicks on a tab or on a button (for the subforms which don't show when the mainform initially loads - ie. there's no use populating a subform if it's not seen until a certain tab/button is pushed.) You can also set the "recordsource" of a subform on any events. Again, I'll use criteria in the recordsource for each subform so that it only loads the record specific to what's on the main form (I don't depend on the "LinkChildField" and "LinkMasterField" properties for the subform.)

Again - read the speed tips in the code bank!! (especially the part about subforms and setting criteria/sourceobject)

*** But also note that since you reported this speed difference not happening until you split the mdb onto a network/shared drive, I'd be more likely to suspect that the speed of the user connecting to the actual network/share drive would be the answer to your question and your current form design needs a "faster" connection to your tables (or the forms need to be modified with the suggestions in the 2nd link below (these really are not any major changes - just a few changes on how you load your recordsets).) Also make sure all users have sufficient (read AND write) permissions to the shared folder where the backend resides (or you'll eventually have other problems.) I'd check with the network guys to make sure everyone can access that shared folder without bottlenecks. The fix above with the subforms is so that you're not "hitting" the mdb file heavily and loading all the tables which it may be doing often if all your subform's are bound to a sourceobject (** when you split the mdb and put the backend on a shared drive, you now are at the mercy of the speed of the connection (and user permissions) to that shared folder and a form loaded with subforms (bound) will have an impact!!).

Note: If user's don't experience slowness when opening other Word/Excel documents in the shared folder (or other slow-type problems), I'd most likely suspect that your current form design (or indexing the tables), needs to be changed. Networks that use daisy-chain hubs are especially prone to slowness to mdb files. I've never had an issue with a switchbox network system. I will only allow 3 seconds for my form/record to open. Anything longer than that, then I'll start looking at a different form design (if there's been no other network issues.)

I will usually have 1 or 2 "bound" subform's (ie. they have a sourceobject and the subform has a recordset) on a main form. Any other forms than that, I'll start using a tab control and set the sourceobject of the subform's (within the tabs) as the tab is changed (or use a "shell" subform with buttons to set the sourceobject of it.) I also use criteria in ALL my subforms as mentioned in my previous post (and in the speed tips in the code bank!!)