Split Databse Running Very Slowly

[exm206]

I have a database that I split. The front-end is on about 8-10 CPU's and the back-end is on one of our network drives. The database is in Access 97 format. The main table currently holds about 170,000 records. There are a few queries and a few forms. The main form opens when the program is started. The probem is that when the user opens the program, Access gets caught up. It locks itself up and the form doesn't open. I took the databse off the network, put everything back into one database, and tried to open it. It took about 15-20 seconds to open, which is a long time. Two important notes: I have the same, exact database(with last years info) on the network and it runs fine. It only takes 5-6 seconds to open and load the load the form(and that is from the network drive.) Secondly, I just noticed today that if I press Ctrl-PageBreak while the database seems to be hung up on the network drive, the form will start loading instantly and will be completely loaded in a matter of seconds. Does anyone know what this means or how to solve this problem? Any help will be greatly appreciated. I already tried a million things to solve this like compacting, repairing, creating a new database and importing all the tables into it, recreating the queries and forms, turning off the AutoCorrect, etc. Thanks a lot for any ideas you may have.

 

[exm206]

I was able to slove my problem and wanted to post it on here. Hopefully this will help somebody else. I went to Tools....Analyze.....Performance. After this did its job, it gave me about 20 suggestions on how to improve my perfomance. One of them was to Index my Unique ID field. This made sense because I have a subform on my main form that links to the main form with the Unique ID field. I set the index on this field and then compacted the database. When I re-opened the application the main form loaded almost instantly. The problem must have been loading my subform because the Unique ID field wasn't indexed. Keep this in mind if you have a similiar problem in the future.

 

[mdmilner]

Thank you for posting your fix. Wanted to add that we weren't ignoring you, just didn't know how to fix it. Your description seemed to indicate you'd tried quite a few things already.

Mike