Jim Lawrence
accessd at shaw.ca
Tue May 3 13:49:39 CDT 2011
Hi John: I agree with you observations. The BE will always corrupt if used by multiple users, but only in certain cases. The most common scenario is if there is more than one copy of MS Access base running on the network. If the FE modules are then bound to the BE, their activities are tracked by a couple values. One of those values is MS Access's product code number, so the lock file system does not distinguish between the connections of two or more FEs and you can see where that is heading. Unexplained crashes and data corruption. Of course a person could try to simply use an unbound set of forms but that will also lead in to another group of problems as a MDB database will be with any real record locking or protection against multiple users access of the same record. That again can lead to data lost and record corruption of which you have so eloquently described over the years. Now if we move to a real SQL BE, those issues are moot. These ACID (atomicity, consistency, isolation, durability) BE databases handle all multi-users using multi-records or even the same records all without any problems. Most of the business logic (queries, reports, modules and extended coding etc.) can be stored there as well. And then the ability to handle transactions is an awesome feature beyond anything designed in an MDB. The final reason for not migrating to MS SQL is also a non-issue as a Free MS SQL Express is there for all to use. (Note: MS SQL Express does not have report building capabilities but a reports can built in its big brother and imported.) Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Tuesday, May 03, 2011 3:44 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Store accdr File Locally or on a File Server? Jim, I know that you likely understand all of these issues, I am just writing them up for the benefit of any of our list members who might not. I see from this email that you are specifically discussing a "script:" and thus are discussing a "copy to local drive" system. If you were discussing actually using the FE from the RAM disk, the problems are as follows: 1) Access doesn't load "the *entire* file" or "the *entire* FE", it loads whatever piece it is using at this instant. For example it may open a switchboard and some code modules to get started. Access thus asks the file store to go get specific pieces of the file which are those pieces. Later the FE is running and the user opens a form. DAO asks the file store to go get the specific pieces of the file which are the form, and perhaps a stored query etc. In order to do all of this, Access creates the LDB (the lock database) and holds the LDB open with entries for itself, for as long as Access is working in the FE. IOW it "locks" the FE (or pieces). It is quite possible for multiple users to hold locks in the LDB at the same time, this is what happens when the users access the BE. While this does work, the problem is threefold. 1) temp tables in the FE suddenly become multi-user 2) Writes to the FE (rare, I understand) can corrupt the file if the write is interrupted (bad nic / cable / user) 3) Because of the locks, it is impossible to update the FE with a new version if anyone is in the FE. OTOH if you are just discussing loading the FE from the ram disk into a directory in the local disk and opening it from there, all of these problems disappear. I have a client where something happens on a regular basis to corrupt the BE. We have never identified what the problem is but in such cases, you would likely end up with corrupt FEs as well. John W. Colby www.ColbyConsulting.com On 5/3/2011 12:00 AM, Jim Lawrence wrote: > Hi John: > > Hmmm, I see your concerns. > > The FE is not running from the RAM disk it is just loading from it and that > is quite a different thing. In most cases the FE, loads once a day, maximum. > The actual package runs on the individual stations. Of course the FE is > locked on the server when someone is working on it and so it should be. > > I have had this working for years, on a product that was originally designed > in Access97 so that dates it. :-) > > The only problem that the script had to resolve was when there are two > people trying to grab the FE from the server at the same time. Then the code > would wait a couple of seconds and tries again and keeps looping until > resolution or until 10 tries and then it fall through and it then fails. (I > used a piece of code from the original Norton's command apps called WAIT.) > Under normal conditions I have never had a failure. > > But for some reason I have never used it with another client...the speed of > transfer from today's servers has always been more than acceptable to the > clients. It always does take a few moments for the user to actually log on > to the MS SQL Server data source anyway. > > Jim > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Monday, May 02, 2011 7:11 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Store accdr File Locally or on a File Server? > > Not so, if you are talking about loading the FE from the ram disk. At that > point you are sharing > the Fe which is bad news. Not to mention that the FE is locked while anyone > is in it making it > impossible to update with the latest version if anyone is in the Fe. > > John W. Colby > www.ColbyConsulting.com > > On 5/2/2011 9:55 PM, Jim Lawrence wrote: >> When loading a crucial file, from the server to all stations and given > that >> the transfer has to be fast, create a shareable RAM disk on the server >> (default is drive Z), assign the appropriate drive letter, make it just a >> little bigger than size of the FE (allowing room for expansion and >> improvements) and load the latest FE into the memory drive. >> >> For all the information you may need see the following: >> http://www.speedguide.net/articles/ramdisk-guide-131 >> >> When workstations log in, the latest version of the FE is transferred > almost >> instantly especially if you have a GBit LAN. >> >> This makes the whole issue of whether to store the FE locally a moot > point. >> >> Jim -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com