Slow Updating table

[dccjr]

I have multiple users, each witha copy of the FE on their computer, writing to a single table (BE) on a server. When there is only one user, everything works great with only a slight pause before the refresh for another record entry. However, when more than one person is entering data, the is a huge pause and sometimes complete stop of the front end. The form locks up (can't be closed, minimized, etc). Sometimes after several minutes, it will infreeze and resume operation.



Has anyone had similar problems and what might be done to resolve this issue. Users complain when they have to wait 2-5 minutes to continue to the next record. Any and all suggestions would be greatly appreciated.

[dccjr]

OK, a few views, but no ideas? Is this an isolated problem?

[Rawb]

Is this an MDB/MDE? Or is it an ACCDB/ACCDE?

Is the database password protected?

Also, if it's an MDB/MDE, are you using UAC (requiring users to log in to the database)?

[dccjr]

It is an ACCDB. It is not passwork protected. I am new to this, so what should it be?

[Rawb]

That's fine. If you're using Access 2007 or newer, then you'll want to use ACCDB for the new features and improved performance.

Unfortunately, I can't think of anything that's likely to be your issue. The only thing I can think of would be that the users are competing for Record locks. The problem is, there aren't any easy ways to deal with that...

If your users don't need to browse the whole Table, you could create a Temporary Table in the FE and have the users browse that one instead. You'd just copy over all the Records they'd be looking at, let them make their changes (if any), and then copy them back to the main (BE) Table when they're done.

Also, if your Table is large and/or poorly normalized, you could try dumping any old records into a "History" or "Archive" Table. Properly normalized Tables should be able to easily handle thousands to hundreds of thousands of Records without any problems (depending on the speed of the computer running the FE).

[dccjr]

Thanks. I am looking at all that you suggested. I think you are correct in that they were competing for Record locks, as they were both working on the same series of reports (different queries, same records). Also, thanks for the suggestion for the History/Archive table.