Multi-user db with lots of bound forms "Kelly" <kelly_darby[ at ]homedepot.com> 31.01.2006 22:47:36 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

Re: Multi-user db with lots of bound forms "Larry Linson" <bouncer[ at ]localhost.not> 01.02.2006 03:12:43 "Kelly" <kelly_darby[ at ]homedepot.com> wrote > 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)? 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

Re: Multi-user db with lots of bound forms "Kelly" <kelly_darby[ at ]homedepot.com> 03.02.2006 14:41:06 Thanks for the response, Larry. [Quoted Text] > 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.