I apologize in advance for the long background. I would appreciate any
advice related to bound vs. unbound forms, or generally avoiding
corruption. I have read previous topics on bound vs. unbound... It
appears that there are very strong opinions for both sides, but no real
explanation as to why.
Situation:
I am now the owner of a very form-heavy multi-user .mdb. By
"form-heavy" I mean that it has many forms, some quite complicated with
subforms. The specs require quite a bit of visual feedback (e.g.
things changing color or disabling based on data entered into another
field). The primary functions of this db are data entry and reporting.
I have not yet separated this into fe/be. Reasons: (1) the whole db is
~10 MB, less than 1 MB of which are the tables. This would require
each user (~30) to install a 9 MB front end, which seemed kind of silly
(10 MB on the server vs. 270 MB spread across user PCs and 1 MB on the
server). (2) This db is used by many teams across a large company.
Each team has their own copy. For administrative purposes, I did not
want to have to deal with 2 files per team, plus rely on each team
leader to distribute updated FE's. [Note: There is no use for having
all data in one file, and each team's file resides on a different file
server, anyway.]
There are ~30 users that will use one file, though not necessarily at
the same time. They only need to do data entry once every two weeks.
It is highly unlikely (but not impossible) that more than one user
would be editing the same record at the same time. I have written some
code that runs on startup that checks a User_Log table. If there are 4
users logged in, it kicks the 5th user out and displays a list of the
current users. Otherwise, it logs the user into the table and logs out
upon exit. If the user exits in such a way that they are not properly
logged out, then they are logged out the next time they open the file
or after 24 hours.
All of this being said, I am concerned about corruption. There hasn't
been any issue so far (after ~6 months of use). I have only MS Access
2000 at my disposal as both back end and user interface. Don't ask
why, just humor me for the time being...
Questions:
(1) All forms are currently bound. Is there any benefit to using
unbound forms if all users are accessing the same .mdb file (no fe/be)?
Am I reducing any potential corruption opportunities if I redesign the
forms (change to unbound and eliminate the subforms)?
(2) Given the situation above, any suggestions on corruption avoidance?
Do I *have* to do a fe/be configuration? It is not necessary to have
more than 4 users accessing the file at one time.
Thanks in advance,
- Kelly
> All of this being said, I am concerned about
> corruption. There hasn't been any issue so
[quoted text clipped - 8 lines]
> benefit to using unbound forms if all users are
> accessing the same .mdb file (no fe/be)?
Probably not. The only advantage of unbound forms _might_ be in case of
"heavy" update activity, and then it would be a performance issue.
> Am I reducing any potential corruption oppor-
> tunities if I redesign the forms (change to un-
> bound and eliminate the subforms)?
Probably not, as you do not seem to have a problem, at present.
> (2) Given the situation above, any suggestions
> on corruption avoidance?
The best collection of links and information on multiuser performance and
avoiding corruption that I know of is at MVP Tony Toew's site,
http://www.granite.ab.ca/accsmstr.htm. Visit that and read carefully.
> Do I *have* to do a fe/be configuration? It is
> not necessary to have more than 4 users
> accessing the file at one time.
No, as you have demonstrated, you don't *have* to split. But, whatever the
load, you significantly increase the chance of corruption if you have
multiple users concurrently logged in to the same front-end or monolithic
database. You may go for months or years without problem, then some
apparently-minor change will result in frequent corruptions.
Tony has a freely-downloadable Auto FE Updater which may be helpful in
keeping front ends up to date. I also have some information on the subject
at http://accdevel.tripod.com.
I am not certain where you got the idea that "four" users was a significant
number -- as far as I know, it is not. There have been posters here over the
years who claim that Access "falls over" with more than four users. However,
the concensus is that those people had every factor in multiuser performance
and corruption aviodance just about as far from 'perfect' as it could be. I
have often said that, with sufficiently bad design and implementation, you
could create an Access database that wouldn't even support ONE user.
On the other hand, with all factors near perfect, we have reports of 100+
concurrent users with satisfactory performance. Even if not all factors are
near-perfect, reports of 30 - 70 concurrent users are not uncommon.
Larry Linson
Microsoft Access MVP
Kelly - 03 Feb 2006 14:41 GMT
Thanks for the response, Larry.
> I am not certain where you got the idea that "four" users was a significant
> number -- as far as I know, it is not.
There really isn't any significance to four other than the folkloric
"4-5" users. As I am knowingly using suboptimal design, I picked four
because it is the smallest number of simultaneous users that would
still be practical.