Multiple tables or multiple mdb files?

[Denise]

My multiuser Access database has 40 users, consists of backend and frontend
files. The backend mdb has 4 tables that contain the data. We also have a
backend look-up mdb. The complaint is slowness. People aren't closing
properly and the system will then crash. Would it make a difference if I
broke the 4 tables into their own separate mdb files. Then have the frontend
link to them?

 

[jahoobob via AccessMonster.com]

Others may know more than I but I believe that whether a table is alone in an
mdb backend or is one of four won't make a difference. Have you checked to
make sure that the Default locking is set to Edited records and not All
records.
I have put timed events on data entry/edit forms that closes them after a
period of inactivity 'cause your problem with users is universal.
What does your BE lookup mdb do and how is it tied to the others?

 

[John Vinson]

My multiuser Access database has 40 users, consists of backend and frontend
files. The backend mdb has 4 tables that contain the data. We also have a
backend look-up mdb. The complaint is slowness. People aren't closing
properly and the system will then crash. Would it make a difference if I
broke the 4 tables into their own separate mdb files. Then have the frontend
link to them?

It certainly would make a difference: it would make your application
slower, more prone to crashes, and would remove the option of
enforcing referential integrity between the tables! I.e. "Don't go
there!"

Check out the suggestions at

http://www.granite.ab.ca/access/performancefaq.htm

instead.

John W. Vinson[MVP]

 

[Immanuel Sibero]

Hi Denise

Would it make a difference if I
broke the 4 tables into their own separate mdb files. Then have the frontend
link to them?

Yes, things will even be slower. Access would then have to open and close 4
files.

Just a couple of comments:
- Do you give each user his or her own copy of the front end?
- Do you use persistent connection to each linked mdb?


Immanuel Sibero

 

[Denise]

I'm going to check the dummy table and form I created and see if it's working
properly. Thanks for the advice.