frontend performance issue

[gol4]

In 1997 I created an Access database to log the calls into our Customer Service Center.
Being inexperienced, I placed the database on a shared network drive and placed a short cut for the staff who log calls, Usually around 3-5, I also have a front end that the various departments can access their calls data (20 +). This has worked flawlessly 24-7 for the past 5 years with around 12000+ calls being logged monthly.
Recently we have decided to take on more departments who want additional data tracked. I looked at this as an opportunity to improve the database with my major goal being frontend-backend for all.
My main form consists of basic information including 4 drop downs based on other tables and 3 text fields that do a Dlookup() once data is entered in a field. This works perfectly until I move the backend onto the shared drive (only the tables).
Everything seems to work correctly the form loads quickly the drop downs are instantious and the dlookups are quick I can navigate between existing records quickly. The problem is when I try to move to a new record I sit there for about 5 seconds and watch as each field slowly clears from the form before it moves to a new record. If I don’t split the database and have everything sitting out on the shared drive it works perfectly. Since most of the time new records are being added this is unacceptable.
The form has its datasource set directly to a table. I am doing this in Access 97 and our network is Novell.
I appreciate any thoughts or Ideas on why this is happening. Thanks

 

[JimHorton]

gol,
I'm assuming you have a primarykey? If not, this is definitely the problem.

If you have this key, then how are you going to a new record--docmd.gotorecord Acnew? You might try setting the form.DataEntry to true, so it doesn't have to traverse from record 1 to the last record to insert a new rec.

Also, if recordlocking on the form is set to all records, or even edited record, this could exceed novells lock limit, which has been a know headache for Access /Novell
--Jim

 

[gol4]

Jim,
Thanks for the response,
Yes I do have a primary key. It is an autonumber that simply creates a recordId.
I usually open the form in data entry mode, It opens fine but when I complete the entry for that record and it goes to the next record (which is new) it slowly clears the form. I have cycle set for all records, recordset type is dynaset and I have it set for no locks.
I can't understand why with the form being on the local drive it should act this way.
Thanks

 

[JimHorton]

gol,
I did a quick test on an .mdb I'm working on. I used the Form Wizard (which I rarely use but did in this case to get a 'plain vanilla' test). My linked table, upon which the form was based, is on the network, and has about 80 fields, and 1.6 million rows. When I set the form to DataEntry = Yes, the form opens right away and after filling in a record and hitting either the >* or the >, to go to the next, new record, it goes there immediately. The only delay, as I mentioned above, will come if I don't set DataEntry to yes, and start on the first record and hit the >* for New record--then it scans the entire table until it gets to the last record, then goes to new.

So I can only suggest looking at AfterUpdate events for controls & Form, and possibly some data-validation rules you have set up at either the Form level or Table level, and it's also possible that if you have many indexes on this table, this would cause a delay upon insert as each index entry must be inserted in it's correct order, although my table had 9 indexes, and 6 of them had 2 or more fields. Let me know if you find anythig...
--Jim