Jim Lawrence
accessd at shaw.ca
Tue May 3 15:29:21 CDT 2011
Just a note; when I was describing this I might not have emphasized that each copy of MS Access, on each station should be a different license. This has nothing to do with FE or BE applications. I personally like to use MS Access for just its unsurpassed presentation capabilities but prefer to secure most other functionality on a reliable MS SQL server which immediately gives unsurpassed security and extended remote capabilities as well. And then can lead to the eventual migration to a web interface. I use ADO-OLE connections for Access to MS SQL and simply had no idea that multi-level transaction management was even possible with basic Access. Show me some code. " And I think you can create SQL Server Reports in SQL Server Express 2008 R2 " ...that's good to know. I have had a few issues on sites but only ones that have matched the site setup as described. (Or is built on a MS SBS with real-time station desktop management turned on... this is where the desktops are stored under each user profile, on the server and is being updated on regular intervals. When the system is under load it results in the bound FE to time-out and disconnects from its BE...very ugly.) Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters Sent: Tuesday, May 03, 2011 12:37 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Is Access a Bad Program? Jim, I have to step in and disagree with you (I think). I, and many other people on this list, manage systems that have multiple Access applications (clients) using table links all to the same Access database (server). An Access application is an .mdb file which contains table links (usually), forms, reports, modules, macros, and queries. An Access database is tables only. Only very rarely have I experienced corruption in this type of system, and only when the server was having problems. If you're discussing having multiple people using the same Access app (FE) at the same time, there's no need for that discussion. It won't work, and if Access is set up correctly it won't be set up that way. For SQL Server, with an Access app, you would not put reports, modules, and extended coding there. Tables, stored procedures, views, UDF's - yes. Also, an Access app does do transactions, and rather elegantly, so I use them where appropriate. And I think you can create SQL Server Reports in SQL Server Express 2008 R2. If what you are describing is actually happening, they you're using Access incorrectly in some way, or on a network with poor hardware, or on a badly configured network. Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence Sent: Tuesday, May 03, 2011 1:50 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Store accdr File Locally or on a File Server? 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com