 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / January 2008General Scability Question for you all
Hello. I have a strange request… I am not looking for any one answer in particular, but perhaps your opinion on how to deal with the following situation… and may be how your company dealt with the following: The company I work for has grown very rapidly, almost to quick. We went from about 350 employees to over 1500 in 3 years. During this growth there was a need by our business units to track or analyze data, and of course no budget to do so. So they went and created their own MS Access databases with no input from the IT department. They have now created over 100 business critical MS Access applications. So now that our company recognizes these applications as being business critical they want to find the best way to assess risk to business operations, and find the best solution to support these application including file backups etc. Have any of you ever dealt with a similar situation? Are there any third party tools that are good for version control/backup for instances like this? Any input would be very well received. I appreciate your time… I already know how I want to deal with this situation, but nobody listens to me ;) More background on our situation: Let’s see, to make things even worse we run a both NOVEL and AD on our network! What a darn mess!!! To say the least our network is not the most stable and I frequently encounter corrupted files… and have run across weird MS Access issues that I have never seen in my 10 years supporting MS Access. Oh yeah, we totally live 7 years in the past and mostly use MS Access 2000, with a few lucky folks having 2003! So a mixed office environment makes my life so much easier too lol. Think that wraps it up… I’m thinking I should just get my resume out there and move on lol. Thanks! 1) It doesn't scale brilliantly. An rigorous analysis of the systems, what they do and and an orderly conversion to SQL server for anything that is likely to really grow is probably the best solution. Having said that, systems that are used by only a few people with databases less than a gigabyte may be better left as they are, albeit with some attention to 3) below. 2) Your real scaleability problems tend to be around numbers of concurrent users, and the probability that if the databases and applications have been created by end users, design will probably be poor. 3) Of course the scariest thing about these sort of systems is that the outputs may not have been preperly tested and may be wrong. 4) Are they actually business critical or do they create critical systems on behalf of their developers > Hello. I have a strange request. I am not looking for any one answer in > particular, but perhaps your opinion on how to deal with the following [quoted text clipped - 40 lines] > > Thanks! Nigel, Thank you for the reply! To me the scariest part of all this is that there were no controls in place to prevent this from happening. Most of these systems just read data from our SQL servers... and do simple reporting... but as you stated these people have no idea how to design proper queries etc... so who knows what the heck they are getting back in their record sets. Some of these sytems store their own data... and again were poorly designed. If I were to fix this. I would remove MS Access from all desktops and then only give access to people who have had their applications reviewed and certified for compliance with Better Practices etc. from IT. It also seems we have a few renegade programmers that created all sorts of code in these apps when they could have used built in MS Access functions... Oh, what a joy this is! >1) It doesn't scale brilliantly. An rigorous analysis of the systems, what >they do and and an orderly conversion to SQL server for anything that is [quoted text clipped - 18 lines] >> >> Thanks! ?And when 1500 people come knocking at your door because you've removed what they consider their mission critical application?<g> "Mission critical" implies that the organization won't be able to function (or at least, not in a timely, cost-effective manner) without the application(s). First, is that TRUE? Does anyone besides the app's creator agree? A Bureau of Standards makes a lot of sense ... if your organization has the resources to create the standards, review the existing apps, establish a process for considering new apps, and most importantly, to refactor and redeploy those existing apps that have to continue working. ?No resources? Why change?! Who (or what) is the driver for this? Is it an "exercise" (we want to know)? Or is it an "initiative" (what will this cost us to remedy, and how do we fix it)? GOOD LUCK!  SignatureRegards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ > Nigel, > [quoted text clipped - 34 lines] > >> > >> Thanks! > A Bureau of Standards makes a lot of sense ... Not if it is headed by, or includes, someone whose answer to a potential problem of data loss is to yank Access (and thus the critical-to-that-user apps) from the desktops and toss it in the laps of an IT department which may or may not have the knowledge to do any better. And, if the poster is from IT, certainly has not demonstrated that the IT function has particularly good judgement. Larry Linson Microsoft Access MVP P.S. I quote a former colleague at IBM, a Consulting Marketing Support Rep, who, in regard to people who complained about project managment software that they hadn't bothered to learn how to use, said, "You've gotta know what you're doing." Ok Larry, I know your MVP Status proves your amazing technical abilitities, but it does not justify or leave room for any sense of humor. My comments about removing MS Access from the users was written with a large smile on my face because it was funny, not because it's a solution. There are still like 2% of IT people that have a sense of humor, and 1% that still have personality. That being said, the real issue here is the lack of intututional control by my company over the past few years. Allowing end users to create business critical applications without guidance from technical individuals will ultimately lead to poor application designs, not to mention inaccurate data retreival etc. So the question posed is how does the company now take control of this situation? How do you tell the dog that has been eating from the table that they now must eat from their bowl? > > A Bureau of Standards makes a lot of sense ... > [quoted text clipped - 12 lines] >that they hadn't bothered to learn how to use, said, "You've gotta know what >you're doing." > That being said, the real issue here is the lack of intututional > control by my company over the past few years. Then it's not a technical problem, but a management problem (i.e., a people problem). Get management on board. That's the only way to resolve this. And I mean the management at the level above *both* IT and the business units (so we're talking pretty high up). It would probably be a good idea to have the head of IT on board, as well as some people high up on the operations side before approaching management, so it doesn't look like a power play. The important thing is to make sure that the users know you have their interests at heart and not just IT's interests. IT is a service, and too often, IT folks forget that. They are you your clients and you need to keep them happy. If you do, you'll have much less difficulty implementing big policy changes like this. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > Ok Larry, I know your MVP Status proves your amazing technical > abilitities, [quoted text clipped - 35 lines] >>what >>you're doing." > Ok Larry, I know your MVP Status proves your amazing > technical abilitities, but it does not justify or leave room > for any sense of humor. Au contraire, MVP status is awarded for contributions to the user community, not for "amazing technical abilities". I'd say that surviving for 49 years in the software side of the computer business was more pertinent. > My comments about removing MS Access from the users > was written with a large smile on my face We can't see your face in the newsgroup, and unfortunately it was not accompanied by an emoticon or a <GRIN>-type comment to give us a clue. > ... Allowing end users to create business critical applications > without guidance from technical individuals will ultimately > lead to poor application designs, not to mention inaccurate data > retreival etc. I have seen some exceptionally fine business-critical applications that were conceived by end users, and, when the technical individuals prioritized them low on the development lists, implemented by those same end users. The only reason they had to be turned over was that the application was taking too much time from the real, business-critical assigned business duties of the implementers/users. Even then, in many cases, the line-of-business management had to contract with outside technical people to take over the application. > So the question posed is how does the company now take > control of this situation? David answered this -- it can't be done at a low level, and taking control may well not mean what you assume that it will. It's a business management issue, not a software control issue. A sad fact is, that by the time the appropriate level of management recognizes the problem and decides to deal with it, the company will often have other pressing problems, like "how to avoid bankruptcy". Sometimes it takes a giant "cluestick" to get management's attention. > How do you tell the dog that has been eating from > the table that they now must eat from their bowl? A good start would be not even, in fun, to compare the people who know and actually run the business to "dogs". You might be surprised how many times I have come in to some company as a contractor, working with the "business operations" part of the company, and learned more about the company's operations in a few weeks than was understood by IT managers and personnel with years and years experience doing the application programming that was supposed to support the business operations side. It was all too clear why the application software did not adequately support the business, and why they had to turn to outsiders to get something done. Larry >If I were to fix this. I would remove MS Access from all desktops and then >only give access to people who have had their applications reviewed and >certified for compliance with Better Practices etc. from IT. 1) So they'll start using Excel. 2) Will the IT department respond to such requests on a timely fashion? Will IT organize the necessary training? Including, when requested, one on one sessions for an hour or two to help users with specific problems? Tony SignatureTony 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 Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ Tony, Thanks for the reply. Since we came to this company we have done several things. First, we took about 2 months and completed an indepth Better Practices guide for creating and supporting MS Access Applications. We then developed an Application Inventory to capture all important information including dependencies, users, file locations etc. With new applications the business Units have now been seeking help. We provide MS Access training as needed from the ground up with these people. The new databases being created are now of much higher quality. The oproblem ultimately is that we still need to inventory about 100 more of these existing applications. There is no incentive for the BU to work with us. They are too busy, they blow us off etc. But when the poop hits the fan. ..who do they call... us. So how do we get them to participate in this inventory process... then comply with Better Practices? This is a hard line to walk when the kids are use to running the house. Jason >>If I were to fix this. I would remove MS Access from all desktops and then >>only give access to people who have had their applications reviewed and [quoted text clipped - 8 lines] > >Tony > The oproblem ultimately is that we still need to inventory about > 100 more of these existing applications. There is no incentive [quoted text clipped - 3 lines] > then comply with Better Practices? This is a hard line to walk > when the kids are use to running the house. Go to management (i.e., the people above both IT and the users) and explain the problem in a non-adversarial fashion (don't blame users -- they are reacting normally). One way to do this is to start accounting for the costs associated with the break/fix model, and comparing it to the costs in proactively fixing the apps ahead of time. If IT bills its work to business units, you could bill break/fix work at a higher rate than pre-emptive re-engineering. This would mean that the managers of the business units would have a budgetary incentive to work with you in revising the apps to meet your standards. And there's nothing punitive or unreasonable about this approach -- it costs much more to fix things in an emergency than it does to revise ahead of time at your leisure. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ >Thanks for the reply. Since we came to this company we have done several >things. First, we took about 2 months and completed an indepth Better [quoted text clipped - 5 lines] >provide MS Access training as needed from the ground up with these people. >The new databases being created are now of much higher quality. Fabulous. Sounds like you're doing the right things. You're taking the wind right out of my sails. <chuckle> >The oproblem ultimately is that we still need to inventory about 100 more of >these existing applications. There is no incentive for the BU to work with >us. They are too busy, they blow us off etc. But when the poop hits the fan. >.who do they call... us. So how do we get them to participate in this >inventory process... then comply with Better Practices? This is a hard line >to walk when the kids are use to running the house. Yes, the kids, from the IT perspective, are running their rooms in the house. However they know their business much better than you do. And their approach is quite understandable. As to how to get them engaged? I'm not at all sure what is a good or even decent approach. Can you assign a BU someone for a week to spend on site cleaning things up and doing one on one training? Tony SignatureTony 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 Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ > Thank you for the reply! To me the scariest part of all this is > that there were no controls in place to prevent this from [quoted text clipped - 4 lines] > Some of these sytems store their own data... and again were poorly > designed. If it hasn't brought your SQL Server databases to their knees, that's pretty much credit to Jet's good design, as it guesses pretty darned well in most cases how to efficiently send queries to the server. You might consider the SQL Server reporting services. I think they are designed to allow you to control what people have access to (but I've never used them, so could be wrong). > If I were to fix this. I would remove MS Access from all desktops > and then only give access to people who have had their > applications reviewed and certified for compliance with Better > Practices etc. from IT. Ack! That's a TERRIBLE idea! Access is a terrific front end to SQL Server, and the fastest way to give people access to data they need on a regular basis without having to go through IT for every little data request. If you have proper monitoring in place, and have set up security properly in your SQL Server databases, and have designed appropriate views for your users to use, there oughtn't be any real issues. You can monitor who is using what resources and determine where the inefficiencies are and then help those users fix those inefficiencies. > It also seems > we have a few renegade programmers that created all sorts of code > in these apps when they could have used built in MS Access > functions... Oh, what a joy this is! That could be because they were ignorant of Access's built-in functionality, or it could be because *you* are ignorant of what their code is actually doing. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ I think 100 is not too many. They should all immediately be brought under version control (Visual SourceSafe, but not at the object level), so the source code (=intellectual property) is preserved. I am assuming you are already making backups. A competent Access developer can write some common code to be included in each application. I am thinking of standardizing versions, error handling, line numbering, of course splitting the app in frontend and backend, logging who uses the apps, the bare basics. Database design reviews can be scheduled after that, and applications can be prioritized for review, upgrade, redesign, consolidation, etc. One day you're going to want to store the data in SQL Server, and use Access (and perhaps other tools such as web-based) exclusively for the front-end. -Tom. >Hello. I have a strange request
I am not looking for any one answer in >particular, but perhaps your opinion on how to deal with the following [quoted text clipped - 29 lines] > >Thanks! > One day you're going to want to store the data in SQL Server, and > use Access (and perhaps other tools such as web-based) exclusively > for the front-end. On an unstable LAN, you might want this sooner rather than later. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > I'm thinking I should > just get my resume out there and move on lol. Gosh, I'd see that situation as an incredible challenge and look forward to coming into work every day! What I'd do is survey the users, but you want to make sure they know you're Access-friendly. The worst that ought to happen would be a conversion of the back end to SQL Server and a revision of the front end to work with that. The best that would happen would be that all those apps would finally be brought up to best practices by a professional developer. By starting with surveying the users, you're showing that you care about their point of view. Then you'd want to somehow check all the servers and all the workstations. There are tools with workstation management programs like Panda that allow you to run scripts on the workstations to collect information about them. Also, I'd suggest that you work with IT to get a single version of Access (if not Office as a whole). That should be fairly inexpensive if you go into the Open Licensing platform. And if the network is unstable, you really should consider moving back ends to SQL Server as a must. I would see this as a golden opportunity for an Access developer to work as a negotiator and miracle worker between the IT department and the end users. With IT, you'd advocate for the users' needs. With the users, you'd advocate for IT's policies. Of course, that points out that the problem is as much a people problem as a technical one, but most computing issues come down to that in the end, anyway. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
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.