Excessive time when editing forms

Topic

New Reply

I run an Access 2007 FE/BE system with name autocorrect turned off.

When editing a form and clicking into a subform, I get the busy circle for close to 2 minutes before I can do anything and the subform becomes editable. It also takes as long when clicking back on the main form.

The BE sits on a NAS box and there are no more than 6 of us connected to it across a network neighborhood (we don’t run a server).

Anyone have any ideas as to why it gets so slow?

Reply | Quote

Replies

Does each workstation have it’s own copy of the front end? If not, that could be an issue. But more than likely the issue is with the data source for the subform and the form. Have you checked to make sure all of the key fields have indexes, and that in particular that the subform control that links to the main form record are indexed. And it could be just the number of records for both the main form and the subform. If you are approaching 100,000 records that is lots of data to pull across the network. For that reason we nearly always show a single record on the main form, and no more than a few records on the subform. Finally, it is possible that the NAS is contributing to the sluggishness. Is the BE being compacted and repaired on a regular basis? If not it may be approaching the upper limit on the size of a single Access database. This is sort of random suggestions, but hopefully one or more will help. Let us know how you get on.

Reply | Quote

Wendell,

Each user has their own copy of the FE. The form shows only 1 record at a time and only a max of 10 records in the subform. Only 13000 records in main table.

Tell me more about indexing, it seems to be one of my knowledge holes.

Also, when I say form editing, I mean form creation/edit mode, not live record data editing.

Reply | Quote

Indexing has to do with the design of tables, and it provides a quick way to find records in one table that link to a specific record in another table. It is a property that you set for a field in the design of a table, and is a Yes/No type of property. The “primary key” is a special kind of index that is set for a table and is generally a unique value for each record and is indexed.

On your last comment, I’m a bit confused. Do you mean that you are going into design mode of the form and then clicking on the subform that is embedded in the main form? If so that is a real puzzle….

Reply | Quote

Do you mean that you are going into design mode of the form and then clicking on the subform that is embedded in the main form? If so that is a real puzzle….

Yes, this is exactly what I mean. Sorry I didn’t get back earlier.

Reply | Quote