|
vb123.com |
|
|
Home Contact Us Order our Software RSS &
Newsletter
Get Good Help
The
Workbench
Find out who has your database open, start the
correct version of Access, easy compacting and backups, change startup
options, mde compile, shutdown database
Read and
Download Garry's Blog
About The Editor
Garry Robinson
writes for a number of popular computer magazines, is now a book author
and has worked on 100+ Access databases. He is based in Sydney,
Australia or try our new Expression Web |
Is Your Database Corrupt ?
IT Departments and Microsoft Access ~ Developers Have
Their Say
By Garry Robinson and Numerous Access Professionals
In the editorial of my latest newsletter, I posed a question to the readers (and got a lot of responses)
Whilst I have been discussing Access databases with managers and IT staff this
year, I have often heard the phrase "We don't support Access databases". I then
ask the question, do you support Excel and the reply is "yes but we don't
support macros (VBA)". So here are three questions to YOU, the readers.
Have you noticed this behavior?
What parts of Access should IT staff support as a minimum ?
What should IT help teams know about Access so that they
can provide at least some support?
Garry's Recommendations
IT departments should realize that Access is a good tool for organizing
information, whether that be in a formal manner through a professionally
designed database or as a simple database setup by a user with modest Access
skills.
IT Departments should be good at installing Access and be aware of important service packs that relate to the version that their company has installed and issues like making sure the Link Table manager and other Add-ins are available to users.
IT Departments should ensure that Access databases are compacted at regular intervals. This should happen once a week for important databases. They should do this by telling every person who discusses an Access database where the Compact option resides in the menus and by using tools like The Workbench to help manage important databases.
IT departments should be able to articulate to users how to manage the macro security environment they have selected for users. If using Access 2007, this should include explaining Executable Content and Trusted Folders. Read my MSDN article here
If IT Department want to understand Microsoft Access, make sure that members of the IT department have set up a database and are actively using it. Direct interaction is the only way that they will learn Access. For example using Access to interface SharePoint information will save the IT department development costs.
IT Departments should outsource Access support to professionals if they feel they need the skills but cannot manage it themselves. Just having one good Access expert around for a couple of days a week can help all the people using Access in the vicinity of the developer, not just the database the expert is working on.
Here are responses thus far
| Comments from Access Developers all around the World | |
|---|---|
|
Peter "The simple problem with MSAccess is that anybody can create a database and call themselves a software developer. There are too many poorly designed databases which are causing companies a lot of grief. I would recommend certification for MSAccess on it owns. The simplest suggestion is recognition as a MSAccess Professional. This way MSAccess could be recognised as a serious database and development platform. Jack "Have you noticed this behavior? Yes, many times. Practically every firm (both government and private) don't wish to go near Access. On rare occasions we have run into clients/students who say that someone in their Help Desk support area has been able to help them, at least partially. In those instances the Help person has already created a database for themselves and have been able "to relate". What parts of Access should IT staff support as a minimum ? Personally I think IT support should be capable of showing/helping users in creating effective tables since good tables properly normalized are a major basis for a 'good' database. Also query basics would be helpful. What are three things that IT help teams should know about Access so that they can provide at least some support? 1. Good table design including primary key use and normalization through at least 2nd normal form. 2. Query design and criteria use especially with the combination of wildcard use, particularly the asterisk (*). 3. Report wizard understanding, then basic understanding of Report sections, as well as elements such as 'changing a label in place', adding a logo. I don't know how many times we have discovered Help Desk personnel telling end users to learn Crystal Reports to do Access reporting. Incorporating other software to avoid doing reports in Access is ridiculous. The Access report generator is so cool and we've never encountered any thing it couldn't be taught to accomplish. David "We use Access to a great extent in our organization. IT is not very supportive. We generally know who the Access gurus are within the organization (I'm one) and have a informal network of problem solving. My department has posted tutorials for working w/ Access using the data that we provide via SQL Server databases. There are also plenty of books and classes one can take. The only Access support we expect from IT is installation support. Access is too complex an application to expect support from non Access users and IT folks don't use Access or use the the data we use. They maintain systems and networks, plan for future needs, maintain user accounts and security. If an organization wants support on any more then the basics, in terms of software support, they need to provide educators to handle this task, not expect IT folks to do this. Arvin "I've come to the conclusion that there are 2 types of IT folks. 1. Normal people who do their jobs, and learn what they need to support their networks. Type 1 individuals are basically people who are helpful and supportive. They recognize that their role is to enhance the business process, not supplant it. They do no feel threatened by challenges. 2. And then there are the network guys who need to control everything. They will support new apps only if they chose them and they only choose apps which make their job easier or give them even more control. From a business prospective, the main difference between the 2 is the health of the business. Type 1 Admins are looking for every opportunity to enhance the business, while type 2 individuals think they are the business. Apparently you've run into type 2. I've been fortunate enough to only work with type 1's this year, although I've had the great displeasure of working with type 2's in the past. I now refuse the work if I sense control freaks. It just isn't worth the aggravation.-- Mark "Quite frankly, I'd rather NOT have the IT staff try to support my databases! I will set-up my clients with a backend on a server and each user having their own copy of the front-end. Later, I will walk into a client's office and find that someone has messed with this. For example, the shortcut I've established on the user's desktop has been replaced by a shortcut to the master copy of the front-end on the server! Sometimes they do this because they just didn't realize what was happening, but other times because they think this is "better"! I have one client where I left detailed written instructions on how to set-up a new computer for accessing the database. Their hardware guy blithely ignores these instructions EVERY time. This has been happening for years! Bill "I guess I'm the most qualified IT person in my department to answer this. We don't support Excel in any way other than the installation of the software which comes pre-installed on all our machines anyway. No macro writing, no template design, nothing. As to Access, before I was hired, IT had one or 2 people with power user experience who helped users create reports in a few databases that the users or someone in their department had built, but it was always a favor, not an actual duty of the IT dept. Once I was hired, that all changed. I took on support along with development for all things Access. I only support the ones I redesigned or databases I developed myself. We do not support any databases created by people within the hospital. Kinda like "You made your bed. Now sleep in it." Those databases can be requested for redesign by me which would then put them in the realm of IT support. That all makes sense to me from a support perspective. Unless the IT department owns the database it really can't spend the man-hours to make it work right when it never worked right from the beginning. Tom: At The Boeing Company (at least in the Puget Sound area--I cannot talk about other locations), IT support for all of the Office applications is limited to installation, and troubleshooting of system-related errors, such as a wizard that refuses to work. Any other support, such as how to create an array formula in Excel, design a database in Access, write any VBA code, etc. is up to the user to find help. Several years ago, there used to be an organization that one could go to to get up to 40 hours of free help without first establishing a charge line, but we haven't enjoyed this level of support since the mid 90's. These days, when people call the Help Desk for support, the folks there will often times try, but simply don't know the answer. I've personally experienced having 2nd level Tech. Help folks pass their Access questions on to me many times. There are about 20 private Boeing e-mail distribution lists that employees can sign up for, which cover various subjects. Two of the more popular ones include: DL DevTalkOffice (covers Word, Excel, PowerPoint, Visio, Project, etc.) andDL DevTalkAccess (covers Access, of course) There are also lists for SQL Server (DL DevTalkSQLServer), VB Classic, VC++, and a host of other topics. I naturally tend to answer a lot of questions on DL DevTalkAccess. On the lists, Yes, the lists are Boeing supported. One must be either an employee, a contract employee, or be associated with a related support company in order to sign up. I think we still have quite a few people on the list who are located in Wichita, Kansas, who used to be Boeing employees, before Boeing sold the operations there to Spirit Aero systems, a Canadian company. Mike " I work for a UK Government department where the IT support is contracted out to a three letter acronym. The company is the prime purchasing route for all IT. The support includes desktop, laptop, remote access, desktop applications, servers and network infrastructure. Essentially the contract for support provides for 'best efforts' for anything they do not 'own' or have constructed under contract. This means that any staff having problems with writing or debugging code written by themselves or another, are on their own. It also means that if a bespoke database or spreadsheet is required and the users do not have the skills to develop a solution there is a development cost to be contracted for and added to the bill. We have many solutions provided to us by specialist contractors, as they have done so for several years. These are dying into a hard core of experts, as where the contract comes up for renewal the acronym is allowed to put in a bid. The primary supplier is not always successful. For most day to day work this is not a problem as there are a number of expert users around who have the skills to handle small systems development. These still have to go through a development process to justify the work and usually are used as a test case prior to extending into a larger development process. A issue can be use of development servers, which are not available to staff for database work, and the constraint of using MS Access or Excel. It would be beneficial to use Microsoft SQL server is some situations. The current working environment is such that being a developer is very hard work. For example no one has a permanent desk, all work is saved to file servers and not all desktops are loaded with more than the basic Office products which does not include Access. Anyone could be in the seat I occupied yesterday. A feature of current reorganisation is to employ only key workers full or part time. This is beginning to mean only policy experts are retained, any 'support' staff are contracted in when needed. Thus the IT knowledge and development skill are being eroded for the core staff and increasingly IT development is being contracted out, and that includes all IT training. Interesting times. What parts of Access should IT staff support as a minimum ? Advise on product suitability for project being planned. Installing and uninstalling and testing there is a operating product. Provision of correct NT/desktop permissions for operation of product. What are three things that IT help teams should know about Access so that they can provide at least some support? Differences/benefits for upgrade between last 3 versions and service packs; Directions to a least a dozen MVP and quality Access and VBA web sites where help can be found; Advice on the key elements of stepping into product development, like a planning and documentation tool. >Comment You may have divined from my comments above that I am not averse to the changes taking place. I appreciate what is happening and why because I was the Local IT manager for about 150 people for several years trying to control the chaos that ensues where uses are given lots of toys. I welcome new developers to my desk. They leave, I hope, with more that what they asked for. Namely links to helpful web sites and a few pages for documenting the development process. >In closing So good to see you (Garry) gained the MVP badge. I should have commented on that great result a long time ago. I've seen your good work from afar for the best part of 10 years. Very well deserved. |
Christopher "Our agency has taken an extreme measure to stop the proliferation of user developed MS Access databases by removing the Access application from the standard software image on computer workstations. The current software image installs the run time version of Access and not the full version. The rationale offered for this action are: (1) Access databases on the network consume too much of the data pipeline, (2) IT does not have the resources available to support user developed databases, and (3) Not installing the full version of Access saves the department money since most users do not use it. If the IT department was able to support the needs of the users in a timely manner, the users would not need to create their own databases to efficiently manage the agency’s business. Instead, they have disempowered the workers by taking away a powerful tool and replacing it with a process to establish IT programming priorities which requires completion of form that is sent to an IT Governance Committee. This committee meets monthly to review requests and allocation IT development resources to projects they deem worthy. Unfortunately, the IT dinosaur cannot react in real time to the changing business needs of the users in a timely manner, so they took away the one tool which allowed the user to get the work done themselves. I have a request in with our IT Governance Committee since last May and I still have not received a thumb up or a thumb down response. At least with Access, I could have finished the task and be doing something else useful. Scott:
The Embedded Expert and
Hidden Cost of the Database "Often an organisation has a self taught Access guru who recognises the value of converting spreadsheet enabled processes into a database. The database is often a labour of love and has been improved as the staff member reads, learns and experiences, often in their own time. The end result can be several databases that have some foibles, but that successfully support mission critical processes in the organisation. The staff member is often driven, smart, curious, and able to work on their own with little supervision, hard working or just filling in time till the next good job comes along. Whatever they are, they usually end up moving on from their current position. Either upwards in the organisation in to a management role, or outwards to a new organisation in to a management or IT role. This leaves the organisation with a healthy Access Database(s). But organisations, business and staff grow. So without that staff member constantly tweaking the databases to keep up with change, it stagnates. Eventually the Databases don’t support the process anymore. Critical mass has been reached. The old staff member is usually willing to answer calls and help out, initially. But unless a service agreement can be reached this is a short lived arrangement. The organisation is not likely to compensate their old expert, and the old expert most likely has a new and time consuming position. Management have a database that supports the business unit well, but it requires change. The Free on site developer and help desk is now gone. What often makes the Access database appealing to the department manager is that the real cost of the database development and maintenance has been hidden. The Staff member has utilised their own personal time, as well as time allocated to their main position (because they are competent and able to complete their duties faster then expected or other staff help carry the load.) No capital or operating costs have been allocated towards the database. This often leads to kudos for the Department manager as well as their department has been able to achieve their tasks as well as develop a database application to assist their process with no tangible cost to the organisation. This hidden cost can actually be a downfall when seeking a budget to change or improve the database. Some databases have 1000s of man hours at a zero cost on the organisations books as it was absorbed in the staff members usual cost allocation. Options to facilitate Database change to match organisation and business needs The options for the manager are now; a) buy some other off the shelf software, sometimes at a factor of 10s to 100s of thousands more than what they have in real development cost (not hidden cost). b) Ask the IT department to help out. But this usually wont occur due to the following reasons; · IT departments often don’t have staff that are familiar with Access and have poor perceptions of the software, either way they can’t support without adding additional staff or skill sets, some departments just don’t carry Database trained staff, keeping only Network, Hardware and systems Support staff, most of who are able to set up a SqlServer database but not develop one. · IT departments like to have software and databases they can control and manage remotely. Because Access data is often stored on a local drive, the IT department can not guarantee backups, and they just don’t feel comfortable with the security. · There is often little or no documentation with access databases, both technical and user guides. So IT departments have to invest enormous time learning the databases just so they can support it through their help desk protocols. · IT departments often have good scripter’s but lack staff with software development or database design and development skills, so they often cant help with tweaking the database. (IT encompasses a wide variety of skills and knowledge, most IT departments can only afford to focus on skills that keep the system up and running, with specifics on operating software, servers, networks, mail and back ups.) · IT departments fear Access has no security. This is a false impression as Access can be protected to ensure that data is managed properly. When you also realise many SqlServer installs still have User:SA pswd : Default i.e. NO security this becomes a farcical argument. Unless SqlServer is set up with security in mind, the same as Access, then it also suffers from a lack of security. Also often the applications that access the SQlServer database don’t have security. Garry has actually written the bible on Access protection/security. c) Convert the Database to SQL server and have IT or a preferred contractor help out, but the cost of this can be enormous and the database may not need or require conversion. There are many documents and rules of thumb for when a database needs or should be converted to SqlServer (or another RDBMS). d) Seek out a contractor through a preferred employment agency provider. This often does not lead to the right person being hired, but does at least start to progress the database towards where the organisation is now. Often the database (S) only require small amounts of work, but the organisation pays for several months work. This is often the only choice for some departments due to organisational policy of only hiring temporary staff through preferred agencies. Unfortunately temporary staff may not be what is required, it does have a benefit of bringing and on site expert back into the system for user help. A budget for temporary staff may be easier to achieve then a purchase order for a consultant. e) Revert back to spreadsheets – whilst this is not ideal, it does happen. Lack of budget and expertise leaves the department no choice. f) Contact a database developer like GR-FX either for a small job to do specific tasks to the database or for ongoing support and maintenance, or even revision. Whilst this often has a higher dollar per hour cost then say the temp from an agency, the over all cost is often less as the consultant will often have the work done faster and limits them self to only the specified tasks. g) Often F is a good solution on a long term ongoing support basis, as a developer can work in well with the IT team seamlessly to provide support on Access, SqlServer and Excel issues and support the IT department rather than working contra to them. In an organisation with many Access databases – but not enough to hire a full time expert or for rolling 3 month contracts with a temporary contractor, the consultant can also provide support on other databases as well as the initial one . If F or G is the option, it is also wise to have the developer take a critical look at the database and refresh it. Often the database will be built from an expert user’s point of view, which means it is likely to have Low useability, Low Learnability and have many work arounds that the original user/developer was happy to live with or did not realise might be an issue for brand new staff with no user manual or guide. It would also be a good time for the developer to provide some technical documentation on the data model, and possibly the data flow to ensure that the all the processes are being supported still, as well as providing a frame work for the developer to work from and the client to understand their database and where it needs to go and likely time costs. This part is important as the original cost of the development of the database is often hidden in the original staff members costs, usually low in the scheme of things. The technical documentation also provide a starting point for any contractor, consultant or employee that later develops the database. If need be a continual improvement management plan can be put in place to gradually improve the database and software whilst other more critical issues are resolved. This will have benefits in decreasing lead time for new staff to learn the software, and for IT to perhaps handle initial Help desk queries. Brad Burke (970) 222-1501
Thanks everyone for your input.
If you have a comment that you want to bring up on this
topic, send it to Garry |
Other Pages at VB123.com That You May Want To Visit
Track all
changes made to a record in Microsoft Access
Tuning Up Your Design
Toolbars In Access 2000/97
Processing E-Mail
Orders using Outlook and Access
Click on the
button for the next
help page in this Access Loop.