I've just spent some time looking through FAQ sites and searching the google archives of this newsgroup, but I still haven't been able to find a clear explanation of an issue with multi-user databases.
Essentially I have two questions;
1) Does the system.mdw file have any significance to multi-user sharing of an Access 97 database other than security?
2) Can any number of users open an Access 97 database using the same account from the system.mdw without corrupting the database?
What I have been doing up until now is trying to ensure that each user has a unique account in the system.mdw and that any user connecting to the database uses the same system.mdw. If it's not necessary for each user to have a unique account, it would be much simpler to have a single generic user account and a single standard desktop shortcut to the front end.
I can't find any resources which clearly explain the locking system in Access. My impression is that when a user connects to the database, an LDB file is created which contains the details of the connection. The information stored in the LDB file as shown by the Microsoft LDB viewer tool appears to be related to the computer name making the connection *not* the user account from the system.mdw, so presumably it shouldn't matter if the same account is used on several different computers because the locking is unique to the PC name.
What *does* seem to cause problems is failing to use a system.mdw at all; which means that several users of an unsecured database are connecting as "admin" from the system.mdw in c:\windows\system32. In terms of rights to objects, security is not of particular importance in the databases in question.
Having standard desktop shortcuts which include the login account as part of the command line would make distribution of the applications much simpler.
If anyone has in depth knowledge of how the system.mdw file and the ldb files behave, please let me know whether the setup for these databases can be simplified...
Rob Geraghty 13 November 2004 03:24:52 [ permanent link ]
Keith Wilby <keith.wilby@AwayWithYerCrap.com> wrote in message news:<Xns959F5564D88A8keithwilby@10.15.188.42>...> the_harper@yahoo.com (Rob Geraghty) wrote:> > If it's not necessary for each> > user to have a unique account, it would be much simpler to have a> > single generic user account and a single standard desktop shortcut to> > the front end.> What do you mean by "the front end"? Each user should have their own - if > you have multiple users accessing the same FE then this might cause > corruption.
Why would it cause corruption? We have a stack of databases where users open the same front end file on the server, and they don't get corrupted all the time. The usual reason they become corrupted is that an individual PC had a network problem and was rebooted without exiting the application in a clean way.
Using a single front end on the server eliminates the need to distribute the files - you only need to distribute the shortcut. Updating the front end means changing it in one place. Loading the front end is a little slower but on a 100Mb/s switched network the speed difference isn't worth worrying about.
When the network was slower, distributing the front end files made sense, especially if they were large. But with a fast network, I can't see a reason for it.
Now before people start flaming me and saying "there's your problem", I'd prefer to hear an explanation *why* opening several copies of the front end from the server would cause corruption.
Having said that, as I mentioned we've had a number of databases operating with split front end back end on the server and shortcuts on the desktop for years - without significant problems of corruption. The point of my original post was to figure out if the system could be further simplified by having a single generic user account in the MDW file, and put the account name and password into the shortcut. Since the lock file seems to record the details of the computer connecting to the database, not the account used, it should be OK.
Darryl Kerkeslager 13 November 2004 18:03:15 [ permanent link ]
Once is sufficient, isn't it?
Darryl Kerkeslager
"Rob Geraghty" <the_harper@yahoo.com> wrote:> they don't get> corrupted all the time. The usual reason they become corrupted is> that an individual PC had a network problem and was rebooted without> exiting the application in a clean way.
Wayne Aprato 14 November 2004 01:14:14 [ permanent link ]
Hi Rob. I'm no expert but the people who are say that a frontend on each user's machine is the way to go. Tony Toews has written an excellent utility which makes the task of distributing the frontend child's play. You can get it here: http://www.granite.ab.ca/access/autofe.htm
Keith Wilby 15 November 2004 11:46:40 [ permanent link ]
the_harper@yahoo.com (Rob Geraghty) wrote:
Using a single front end on the server eliminates the need to> distribute the files - you only need to distribute the shortcut.
Using a single FE for multi-user purposes is asking for trouble, you've been very lucky so far. The easiest way to give your users the FE is to have your shortcut execute a batch file which downloads it to their local drive and then opens it. They each get their own copy of the latest FE version and will get a performance benefit too.
Access updates the FE MDB/MDEs while they're in use. Things such as query plans, filters, where clauses and such. A97 was much stabler in sharing FEs compared to A2000 and newer.
Also to put a new FE in place on the server requires waiting until everyine is out of it or kicking them out.
Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Rob Geraghty 16 November 2004 06:32:09 [ permanent link ]
Keith Wilby <keith.wilby@AwayWithYerCrap.com> wrote in message news:<Xns95A2596E23F79keithwilby@10.15.188.42>...> the_harper@yahoo.com (Rob Geraghty) wrote:> > Using a single front end on the server eliminates the need to> > distribute the files - you only need to distribute the shortcut. > Using a single FE for multi-user purposes is asking for trouble, you've > been very lucky so far.
Hi Keith. OK, so it's "asking for trouble", but it doesn't tell me is *why* it's a problem. As I mentioned earlier, we've had quite a few databases running that way over a number of years without persistent corruption. There's also some databases which are running in Citrix using the same file. It's essentially the same thing as sharing the front end file on a server.
The main point of my original post wasn't about sharing a single front end. It was about sharing a single generic account in the system.mdw so a shortcut can be distributed to the desktop. Since the lock file records the computer name, this should be ok. I want to eliminate the need for the users to "login" to MS Access, which is possible given a generic account.
Rob Geraghty 16 November 2004 06:35:55 [ permanent link ]
"Darryl Kerkeslager" <Kerkeslager@comcast.net> wrote in message news:<LrydnXA3BZitvwvcRVn-2A@comcast.com>...> Once is sufficient, isn't it?
"Rob Geraghty" <the_harper@yahoo.com> wrote:> > they don't get> > corrupted all the time. The usual reason they become corrupted is> > that an individual PC had a network problem and was rebooted without> > exiting the application in a clean way.
There's not much I can do in MS Access to prevent servers abending or network switches crashing. I've had very few situations where the database back end couldn't be repaired by MS Access, avoiding restoring from tape.
If I was running databases on a server without nightly backups, I'd be asking for trouble.
Rob Geraghty 16 November 2004 06:50:33 [ permanent link ]
cqdigital@volcanomail.com (Wayne Aprato) wrote in message news:<4d9a5a66.0411131414.2be23806@posting.google.com>...> Hi Rob. I'm no expert but the people who are say that a frontend on> each user's machine is the way to go. Tony Toews has written an> excellent utility which makes the task of distributing the frontend> child's play. You can get it here:> http://www.granite.ab.ca/access/autofe.htm
Thanks for the suggestion Wayne! However, software distribution isn't a huge problem; we can use Zen for that. The Access 97 databases are legacy applications, so the simpler they are to distribute, the better. Putting the front end on the workstations actually causes headaches with security, so it's easier to handle the security in one location on the server and just put the shortcuts on the desktop.
Perhaps I'll find out later why everyone is saying "never share the front end" but at the moment it works for me! If any of these databases had a significant number of concurrent users, I'd probably take the distributed approach.
Keith Wilby 16 November 2004 12:14:10 [ permanent link ]
the_harper@yahoo.com (Rob Geraghty) wrote:
It was about sharing a single generic account in the system.mdw> so a shortcut can be distributed to the desktop. Since the lock file> records the computer name, this should be ok. I want to eliminate the> need for the users to "login" to MS Access, which is possible given a> generic account.
Hi again Rob,
I use generic accounts and common WIFs all the time and have never experienced any corruption* and I don't see a problem with including the username and password in the command line (aside from the obvious security implications).
Rob Geraghty 17 November 2004 07:29:32 [ permanent link ]
Tony Toews <ttoews@telusplanet.net> wrote in message news:<18aip01jeh5fp5k6j79a8rn7rgac8hkon0@4ax.com>...> the_harper@yahoo.com (Rob Geraghty) wrote:> >Why would it cause corruption? > Access updates the FE MDB/MDEs while they're in use.> Things such as query plans, filters, where clauses> and such. A97 was much stabler in sharing FEs> compared to A2000 and newer.
OK, except the databases in question don't have any temporary tables in the front end, and don't use filters. Is there any documentation anywhere on the internet which describes what dynamic components exist within the front end of a split database? We only had the runtime kit for Access 97 so these are legacy applications. Thanks for the heads-up, because I'll make sure I run a repair and compact on the front end anytime I repair the back end of a database.
Also to put a new FE in place on the server requires> waiting until everyine is out of it or kicking them out.
Sure, but with the LDBView program, it's easy to find the computers connected and ask the users to close the database. As mentioned above, these are static applications, so no much chance of the front end being replaced.
For what it's worth, of all the databases we're running, the worst performing application in terms of data corruption is a VB front end to an Access 97 back end. The only other access database that regularly gets repaired is one which includes an ODCB connection to a "mainframe" database. So I haven't seen any evidence of database corruption in purely Access 97 systems with shared front ends - provided that everyone opening the database used the same MDW file.
Having said that, it probably depends on how sophisticated the code and queries are in the front end. None of thse databases are especially complex, and none would have more than a handful of concurrent users.
Thanks to everyone who has responded to the thread. I'll pick a database and try setting it up to share a generic account.
So I haven't seen any evidence of database>corruption in purely Access 97 systems with shared front ends ->provided that everyone opening the database used the same MDW file.
A97 is much more stable than A2000 in sharing the FE.
Having said that, it probably depends on how sophisticated the code>and queries are in the front end. None of thse databases are>especially complex, and none would have more than a handful of>concurrent users.
This is also a factor.
Migrating to A2000 though will cause a lot more problems.
Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
I am in the process of relocating a 300 mb Access 2000 FE/BE application from our LAN to a Citrix server. The application has been used for 3 years with 7 current users. It continues to grow, slow and I hope to speed it up. Reading the threads has made me aware of the perils of using a shared front end. Every user has Access on their PC, but they open the FE on the LAN. Even though the Access version of the db matches what is on their PC and not necessarily what was installed, they are still sharing the FE? Looking at Tony's AutoFE Updater I see how easy it would be to maintain separate FEs on people's PCs. I'm guessing we haven't had corruption problems because while users read shared data, they only write to records in their customer group. Thus no 2 users write to the same record. Our administrator thinks we need just 1 FE. If I want separate FEs on the citrix, I need to install Access in 7 user folders. Does this mean I need to buy 6 more copies of Access even though every user has a local copy on their PC? If so, I'll have to stick with a shared FE.
Rick Brandt 17 December 2004 02:51:25 [ permanent link ]
"neptune" <bsimmons1482@hotmail.com> wrote in message news:1103221006.656183.71950@f14g2000cwb.googlegroups.com...>I am in the process of relocating a 300 mb Access 2000 FE/BE> application from our LAN to a Citrix server. The application has been> used for 3 years with 7 current users. It continues to grow, slow and> I hope to speed it up. Reading the threads has made me aware of the> perils of using a shared front end. Every user has Access on their PC,> but they open the FE on the LAN. Even though the Access version of the> db matches what is on their PC and not necessarily what was installed,> they are still sharing the FE? Looking at Tony's AutoFE Updater I> see how easy it would be to maintain separate FEs on people's PCs.> I'm guessing we haven't had corruption problems because while users> read shared data, they only write to records in their customer group.> Thus no 2 users write to the same record. Our administrator thinks we> need just 1 FE. If I want separate FEs on the citrix, I need to> install Access in 7 user folders. Does this mean I need to buy 6 more> copies of Access even though every user has a local copy on their PC?> If so, I'll have to stick with a shared FE.
On a terminal server you would install Access once, not for every user. They would each be running their own instance of Access from the common installation folder.
Yes, every user needs a Citrix client license, a Terminal Server client license, and a license for Access (or any other software they use on the server), but their existing local installations of Access will cover them for the Access license.
-- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com
If you would like to report an abuse of our service, such as a spam message, please . Если Вы хотите пожаловаться на содержимое этой страницы, пожалуйста .
