list boxes in multi-user environment?

[Judy Ward]

I need to understand how Access functions in a multi-user environment. My
ultimate goal is to reduce the size and improve the performance of my db.

I created a db that is stored on a network drive. Many users have the file
open at the same time. There is one main form with a tab control that opens
by default. This main form takes a long time to load because of the 8 pages
in the tab control each of which have many controls. I prefer to take the
time hit up front rather than have the users launch forms from a menu.

Because I don't really know what is happening behind the scenes, I have many
of the same list boxes (different names) on the different pages of the main
form. The list boxes are provided for the user to make selections as
criteria for the queries (command buttons provided). I don't know if user1
can be making/clearing selections in list boxes in the copy of the db that he
has open while user2 is simultaneously making/clearing selections in the same
list boxes in the copy of the db that he has open.

Before I take the time to design and test this, I thought I would ask if
this scenario is possible. Does everyone that opens the db file have their
own "instance" of the controls?

Any advice or links to articles would be appreciated.

Thank you,
Judy

 

[Jeff Boyce]

Judy

Based on your description, I'm guessing you have a single database file on
the network that multiple users share simultaneously.

This approach is risky, as it can lead to corruption problems, and it is
slower, because each person's PC is loading all the screens and queries over
the LAN, and it leads to contention and record-locking issues.

The preferred approach is to put a copy of a "front-end" (all the parts
EXCEPT the data) on each person's PC, and a single copy of the data
("back-end") on the network share. Each front-end copy uses "linked" tables
to see the back-end data. This approach lowers the risk of corruption, runs
faster, and reduces the contention issue.

That said, there may be even one more speedup available, based on my
interpretation of your description. If you have each of the tabs on your
tab control permanently connected (e.g., the listbox on the third tab has
the Record Source pre-defined), your form will take longer to load. Longer
than what, you ask? Longer than if you added code to the tab control's
OnChange event that:
1. inspected which tab was clicked
2. connected up only those controls (e.g., listbox, combobox, subform)
that live on the tab that was clicked.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Judy Ward]

Thank you for responding, but there is still something I want to know. It is
possible for me to put the controls on common ground (above the tab control
on the main form). Then based on which tab was clicked I can make some
controls visible and hide others. But it will cut down on loading time if I
have only one list box of 134 Elements rather than three list boxes of
Elements.

--> Is there any conflict if user1 is selecting Elements for his query and
user2 hits the button that clears all selections? <--

By the way, I did try splitting the db into a front-end/back-end and having
a user in a remote location check to see if this improved performance. He
said it was worse!

I do make a copy of the db every night. And I perform a compact and repair
every night. I also have code that kicks users of out the db because there
is always someone that leaves for the day with a copy of the db open.

Thanks again for responding,
Judy

 

[Jeff Boyce]

Judy

There are any number of network issues that can interfere with performance
of a split database design. Speed of the network "pipe" is one, and I
recall shifting a database to a split design and having screens take 4
minutes to load! It turned out that the antivirus on the network was
"helping", making sure that the database was not evil.

If you are sharing a single copy, located on the network, I imagine (note:
no first hand experience) that there WOULD be contention/issues with having
two folks trying to launch the same listbox at the same time.

This would be another reason to reconsider splitting the application... a
local copy of the form (and listbox) would mean that each user was loading
his/her own copy, not a shared one.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP