 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / November 2004Each user their own copy of the Database ?
I have read elsewhere on this forum that Access 2000 ( or later ) now requires that each user have their OWN copy of the database ( macros,forms,queries,etc) for Access to function properly in a multi-user environment. IS THIS TRUE ???????? Our Access 97 database functioned flawlessly for six YEARS in a 12 user environment with ONE COPY of the database on the server. >I have read elsewhere on this forum that Access 2000 ( or later ) now >requires that each user have their OWN copy of the database ( [quoted text clipped - 3 lines] > Our Access 97 database functioned flawlessly for six YEARS in a 12 user > environment with ONE COPY of the database on the server. No, it's not true at all. We have 28 users running on one copy of Access 2003. It is true that you need a license for the total number of people who will be using it at one time. That's a legal issue, though, not a technical one. Tom Lake You were extremely lucky. It is definitely advisable to split the database into a front-end (contains the queries, forms, reports, macros and modules) and a back-end (containing just the tables). Only the back-end goes on the server: each user gets his/her own copy of the front-end, preferably on their hard drive. This was true for Access 97 as well.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > I have read elsewhere on this forum that Access 2000 ( or later ) now > requires that each user have their OWN copy of the database ( [quoted text clipped - 3 lines] > Our Access 97 database functioned flawlessly for six YEARS in a 12 user > environment with ONE COPY of the database on the server. Tom Lake (above ) relpies this is NOT necessary. Naturally, each user has a copy of Access installed on their physical computer. HOW does making two MDBs with linked tables prevent corruption of the database ? Luck ? If the front end is stored on each user machine, how does one perform a change to the system?Walk around to each machine on the network and change the code ? > You were extremely lucky. It is definitely advisable to split the database > into a front-end (contains the queries, forms, reports, macros and [quoted text clipped - 10 lines] >> Our Access 97 database functioned flawlessly for six YEARS in a 12 user >> environment with ONE COPY of the database on the server. > Tom Lake (above ) relpies this is NOT necessary. > [quoted text clipped - 5 lines] > change to the system?Walk around to each machine on the network and change > the code ? You change the code stored on the server and copy it to each machine on the network. You can do that from your desk without having to leave your chair. Tom Lake Does it matter why the chance of corruption is lessened? It's doubtlessly got something to do with the fact that multiple users aren't contending for the same resources: that any temporary objects that need to be created when open recordsets, etc. are localized in each user's database. To ensure that everyone always has the most recent version of the front-end, you can use the Auto FE Update Tony Toews has (for free) at http://www.granite.ab.ca/access/autofe.htm SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Tom Lake (above ) relpies this is NOT necessary. > [quoted text clipped - 20 lines] > >> Our Access 97 database functioned flawlessly for six YEARS in a 12 user > >> environment with ONE COPY of the database on the server. > Does it matter why the chance of corruption is lessened? It's doubtlessly > got something to do with the fact that multiple users aren't contending > for > the same resources: that any temporary objects that need to be created > when > open recordsets, etc. are localized in each user's database. Yes, it matters if you are trying to fix the problem. I have the distinct impression that there is a lot of the blind leading the blind going on here. If temporary objects aren't being properly localized, I't call that a product design error. Why doesn't this happen in Access 97 ? > you can use the Auto FE Update Tony Toews has (for free) at > http://www.granite.ab.ca/access/autofe.htm Thats a pretty nice user contributed solution. Why did the MS people who changed Access 2000 not forsee this problem ? > Yes, it matters if you are trying to fix the problem. I have the distinct > impression that there is a lot of the blind leading the blind going on > here. If temporary objects aren't being properly localized, I't call that > a product design error. Why doesn't this happen in Access 97 ? Well, if you as a developer use a temp table....then all users will trip over each other...right? So, the solution is to install the software on each pc..and you don't have this problem. Fact is, it is recommend practice to install software on each pc. You will not find ONE person in your company (or your IT) department that will suggest that software be installed on the server, and each pc use that software. No one is suggestion to install Excel on the server. You for years have followed the correct and recommend practice of installing Excel on each pc. You also for yeas likely followed this advice for word also. Of course, you can share Excel documents, and even share Word documents on a server, but no one in our industry would suggest that Excel actually be installed on the server. Further, there seems to be a miss understanding here between applications and documents, and software. If you hire some developers to write an application, then that application is installed ON EACH pc. If those developers use c++, VB, or even ms-access, then once again, that software needs to be install on each pc. It seems to me that someone in your IT department does not understand the difference between an application, be it word, or something that YOU create in with a development tool. You use some tools to create applications, and those applications get installed on EACH pc. I think once you spend a bit of time thinking about the differences between a document, or a file with data in it..and something that is an actual application, then this whole issue starts to make more sense. Just because you are developing software with ms-access, and not c++ does not excuse the fact that everyone suggests to install the software on the pc side. (and, I can even bet YOU and YOUR company suggests to install software like Excel and accounting packages on each pc). So, sure, you can put some Excel data (the documents) on the server..but Excel stays on the pc. And, the same goes with applications created with ms-access: You can put the forms, and code and application part on each pc.....and you certainly can put the back "data only" part on the server...just like you do with every other product. The fact that ms-access lets you keep the two together (application part + document/data part) don't mean it is a good thing. Think about other software your company purchases...and how it is installed. Further, while ms-access does lets you run multiple users sharing the same front end...it is a VERY bad idea...and as several have mentioned...you likely been lucky. And, I can very bet that your IT department suggests to install applications like word, Simply Accounting or whatever applications you purchase on EACH PC. Why would the fact that you now start developing software in house invalidate this time honored practice of installing the software, or application part on each pc? Why does purchased software get installed on each pc..and software you develop not get installed on pc. This thinking makes no sense to me. So, if you get a free evaluation copy of some software...you don't install it on each pc..because it was free, or not developed in house? This issue really comes down to understanding the difference between a documents with data, and that of an application that has all kinds of code, forms, events etc. You can try and run multiple users into the same front end...but it never worked for me..and I never found a IT department that told me that installing software on the server is the way to go... To be fair...it does seem that a97 did tolerate this bad practice better...but even then..it was never a good idea. And, to even be more fair, most applications from Microsoft do have what is called re-entrant code, and that means multiple copies can run. Unfortunately, applications created with ms-access don't work well in this regards. SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.attcanada.net/~kallal.msn I don't know what's necessarily the recomended approach but I can tell you that we have our application on the server (front-end & back-end) that all users use. We certainly ran into multiple problems early on because if you tried to edit the code or copy in an updated version, you would almost always lock everyone's program up. So lesson 1 is....once you put the app out on the network....leave it there as is. Never open it in design mode! To install an update, you have to put it out there under a new name. I use the date i.e. MyApp_102304-01 to keep track of which version it is. But you now have the problem of everyone's shortcut pointed to the previous version......right. Well, to get around that, I created a small VB program.....and this is the program everyone's short cut is pointed to. It's purpose is none other than to open the back-end database and open the table I have there "CurrenVersion", and find out the latest version name of the application....and then open that version of the program on the users desktop. The front-end portion of my database also has a table named "localVer" where I insert what version I'm using as well. The way I use this this is I have a routine that runs (via the timer control) every hour. It does nothing but compare the name of my front-end app with the name my back-end app says I'm supposed to be running. The reason I did this is because we have apps running in a production evironment where the users never close the program, unless they're prompted to.....i.e. There's a new version of this program available. Please close this app and re-open the program to access the new version". Again, I can't tell ya what's right or wrong....I'm not an expert. But this has worked flawlessly for me for years.....access97 through 2003. I use the same VB program for all my different apps. I just have to rename it each time for each app it's going to used with....i.e. "Start_MyAppName" The only thing I have to change is the associated ini file it reads to find the name / path of the back-end database so it opens the right table to identify the latest version of the right program it's supposed to open. Again......Don't Open You App in Design Mode once it's out in the network. You might get lucky but more often than not......your phone will start ringing! You might be able to do this same thing I'm doing with VB with an access program...I just never tried. The only thing the user sees with my VB program is a Splash Screen.....but what it's really doing is as described above. PS....Good luck with this....I knmow this was a real pain in the ...... until I got this set up. I'm not part of the IS&S group so I never had access to changing everyone's program from my desk and even if I did......It would still screw them up if they already had the program open so this solved it all for me. > > Yes, it matters if you are trying to fix the problem. I have the distinct > > impression that there is a lot of the blind leading the blind going on [quoted text clipped - 82 lines] > Unfortunately, applications created with ms-access don't work well in this > regards. Maybe you would be interested in my Access SwitchBoard database. Its sole purpose in life is to open the latest version of another access database. Written in A97, it is free for the asking. It supports downloading front ends to user PCs or workspaces. Icurrently use it to manage over 15 databases for over 200 users (not all in at one time). Andy (hiestand@highstream.net) > I don't know what's necessarily the recomended approach but I can tell you > that we have our application on the server (front-end & back-end) that all [quoted text clipped - 140 lines] > > pleaseNOOSpamKallal@msn.com > > http://www.attcanada.net/~kallal.msn >I have read elsewhere on this forum that Access 2000 ( or later ) now >requires that each user have their OWN copy of the database ( [quoted text clipped - 3 lines] >Our Access 97 database functioned flawlessly for six YEARS in a 12 user >environment with ONE COPY of the database on the server. ---------Sharing & Distributing Databases ----------------- The best way of treating multi-user Access databases is to split your database into a BackEnd (placed on a Server) and a FrontEnd *.MDE file (placed on each individual machine) The MDE file is made from your development MDB file and doesn't have any code that can be fiddled with, but looks and acts exactly the same as the MDB file. This allows you to keep developing the FrontEnd whilst the data in the BackEnd is still in use by everyone else. And now to why this is the best approach, I'm sure list members will forgive me for re-posting the following, but it has all of the information you need to consider your options. A normal shared MDE or MDB file (same difference for this discussion) has three compelling reasons for being split and having its FrontEnd distributed to each machine. (At the bottom of this post is the procedure for doing the Split) 1) Speed for Users 2) Network Traffic 3) Lockout Protection -- SPEED -- As soon as a user clicks the shortcut to a combined database not on their own computer, the Network has to cope with every GUI instruction and screen read/write/display command that Access deals with. Windows Apps are very resource hungry and all of that traffic, particularly screen paints, is going down the network. Add another couple of users and it really gets messy. The users start to complain about slow queries and slow screen changes and slow rendering of reports. Network Administrators complain about excessive network performance degradation and eventually ban Access. -- NETWORK TRAFFIC -- Of course, all these megabytes of information being sent from the server to each users machine every time a new screen is selected chews up network bandwidth. This is one of the main reasons that Network Administrators and "IT Professionals" have a generic "hate" of Access. Given what I have just explained, their reaction is well justified. In many large sites, Access is banned from being installed as part of Office (I've worked at 2 of them, one of them being the largest governmental IT system in the country) -- LOCKOUTS -- There can be lockups (and possibly corruption) due to more than one person being in the FrontEnd at the same time and doing something dumb. In particular, as we've found out through these lists, an incorrect termination by a user can lock the database from use and it cannot be opened without some work at the shared drive end. Access is generally fairly robust, but having a bunch of people working directly in the same front end file is kind of like smoking as you're gassing up your car ... you might get away with it ...... but .... So by splitting the database, only the BackEnd Tables reside centrally and only the data (simple text) travels around the network. That's a million times less than sending a whole set of GUI screens. So, you deploy a copy of the FrontEnd to each user, . Their own work will be faster and smoother and they will love you for it and the Network Administrators will not lock you out. If you have a good grasp of deployment, or you have a Network Administrator who can do it for you, most systems have facilities for uploading an updated version of an application (in this case an MDE) to the network users as they log in. ---------CREATING A FrontEnd / BackEnd SPLIT----------------- - MAKE A BACKUP OF YOUR .MDB (sorry for shouting but lots of people don't:) - create a blank database - import (not link) all the tables from the "main" one. - This is your "BackEnd" all done - BEFORE taking the next step, place the Backend file in it's final resting Directory on the Network - Make sure that this directory is shared and available to all users - Now to create the FrontEnd. - open the Main database. - delete ALL the tables (is why we made backup) - Tables-->New-->Link (not import) to the tables in your BackEnd. - This is your "FrontEnd" all done. - Smart Money here is to create from your FrontEnd MDB file, an MDE (codeless) FrontEnd for user machines. It's just an extra level of safety. Just deploy the frontend file to the users and it should all work a treat. Brett ------------------------------------------------------------------------------------------------------------ Brett Collings, Management Systems Developer Managing Director Corporate Arrangements Ltd Cheers, Brett ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer I was wondering if you could help me understand your comments about the Speed issue you commented on below. If the FE of the Access DB is on a server, when you click on the application, does it not load into the local machine? If it is actually running on the local machine, why should any "GUI instruction and screen read/write/display command that Access deals with" have to travel across the network? All this is done on the local machine. I do realize that the FE has all of the queries, macros, forms, etc. and when you use any of this, it has to be loaded in the local machine before data can actually be manipulated. I would imagine the bigger problem is the transmission of this information and the lockfile on the server end. That lockfile has trouble coping with multiple users. If the FE is on the local machine, it has it's own lock file rather than a common one shared by all. SignatureTed > A normal shared MDE or MDB file (same difference for this discussion) > has three compelling reasons for being split and having its FrontEnd [quoted text clipped - 38 lines] > Business & Systems Analyst > Management Information Systems Developer The appropriate code to display the form, for instance, has to be transmitted across the wire to the local machine before it can display the form. To be honest, I'm not sure whether the entire form and all its code travels across the wire at once, or whether you go back to the server to fetch the next bit of code to execute when required (so that clicking on the same button 3 times, say, would require fetching the code 3 times). In any case, if the FE is on the harddrive, you save that network traffic. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > I was wondering if you could help me understand your comments about the > Speed issue you commented on below. If the FE of the Access DB is on a [quoted text clipped - 52 lines] > > Business & Systems Analyst > > Management Information Systems Developer >I was wondering if you could help me understand your comments about the > Speed issue you commented on below. If the FE of the Access DB is on a [quoted text clipped - 9 lines] > lockfile has trouble coping with multiple users. If the FE is on the local > machine, it has it's own lock file rather than a common one shared by all. Correct, and unlike most shared files (like a Word doc) Access does not load the entire file at startup. During use it is constantly retrieving more stuff from the file and writing changes back to the file. This is where having the local copy really comes into play. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com >I was wondering if you could help me understand your comments about the >Speed issue you commented on below. If the FE of the Access DB is on a >server, when you click on the application, does it not load into the local >machine? No it doesn't Ted. It's not like Word or Excel, every keystroke is sent to the FE file via the network wire and every response, along with all the info to paint the screen has to come back down the wire. If you talk to some of the savvy Network Guys about this, they can demonstrate and document the load that goes on the system. That's why they first won't support Access in order to discourage its use and if it gets bad, they ban it. The result is that a really fantastic development information tool is lost to most mega-sites and everything has to be done by the serious data boys with the result that any or all of the following = True - IT Dept refuses to do it, it's too insignificant - The cost starts at $100,000 from the IT Dept - It takes 6 months to reach the Project Schedule - It takes 3 months to write - It takes 3 months to test - Everyone in the meantime has either died of boredom or left the place screaming about not being able to get a simple 3-screen database done. Cheers, Brett ---------------------------------------------- Be adventurous, be bold, be careful, be a star ---------------------------------------------- Brett Collings Business & Systems Analyst Management Information Systems Developer every keystroke is sent to the FE file via the network wire and every response, along with all the info to paint the screen has to come back down the wire I believe this is incorrect. The data from the database comes over the netwowrk >>I was wondering if you could help me understand your comments about the >>Speed issue you commented on below. If the FE of the Access DB is on a [quoted text clipped - 31 lines] > Business & Systems Analyst > Management Information Systems Developer |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.