[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