What is wrong with Access?

What’s wrong with Access? The IS guy at my company gives me strong opposition whenever someone suggests creating an Access database. One of his concerns is about archiving and he also mentioned “bogging” down the “system”. I don’t know much about IS but I do know a little about Access, and his claims seem unwarranted. The databases that are created are quite simple and do not use up that much space. Also, archive databases can be created when needed. He is always quick to suggest buying “off the shelf” products. I don’t why we would spend money on a program, support and upgrades for an application that can’t be customized to meet company needs. Also, my co-workers like the Access databases better than any other products they have demoed. Is there something wrong with Access? Does it give the IS people that much of a headache? Am I the one missing the big picture?

Baffled

As far as I am aware, there is nothing wrong with access.

Space can be a problem with Access, so you need to schedule in maintenance to compact the database every now and again.

I have encountered the same kind of problems with IT here, as they do not support Access, but then do not give you access to other database packages that you could use (SQL Server) as they claim you do not have enough skills to use the other package.

As for archiving, doesn't your system get backed up regularly anyway ? Here we have nightly backups taken of the system, as well as periodic snapshots taken at various points throughout the day, so there is no need to create your own backups. Maybe you should check with IT to see when the backups are done.


I think the main problem with IT is that they do not have total control with Access databases, and can not control what, who or how its is used. Off the shelf packages, normally can only be operated by IT, so any control the business has around changes to the package, needs to filtered through IT, call it self-preservation or something along those lines.

I'm sure that some IT person will now respond to these allegations, so this thread may be interesting later on !!!!

Being in IT, I can tell you what we encounter. We do not limit use of Access here, but some of the proplems we have encountered are, network resources can be effected, specially if you r network is tight to start with, but this is not usually a big issue. Support is the biggest problem. Joe Shmoe designes this program, leave to go some where else (another company, gets run over, etc.), than some thing happens and all of a sudden it's an IT problem. This is the biggest problem. Then there is no support all of a sudden. Also Access is limited in how many concurrent users it can support. We have had an Access app. grow and once you get past a certain number of users, it is rather unstable. Then they want IT support for something IT knows nothing about. And the usually the only way out of that is to redevelop the application in something else, which cost money, and no one wants to pay, and, and, and then IT is the bad guy and won't support the app. But that is just my take on it. Like I said, we don't limit users to not using Access, but support of an unknown application can take awhile.

Another reason I think that IT doesn't like Access is that it is such an underused program that a lot of IT pros don't know how to use it and therefore can't support you when you have problems. They know what MS support is like so better to buy a program that they can pawn off the support onto the designer.

Autoeng

Thanks for the feedback guys. Our IS person did mention to some of my co-workers that Access is going to bite them in the @$$ sooner or later. I assume he meant if I leave the company for any reason. I’m sure they could hire someone to pick up where I left off (don’t get me wrong I know its not that easy!). Or they could hire me as an outside consultant . I just wanted to know the IS side of the story so that I am aware of these concerns and can take them into consideration when the time comes. Any other feedback is welcome.

Baffled

The biggest issue for Access databases is that if you keep the whole package in Access (as opposed to Access front-end, something else for back-end data), you run into a few simple but serious problems.

1. Access uses your network as a FILE SERVER, which means if you run a big, whompin' query, you have transfer a LOT of data over the network. That is, the query is processing remotely-held data on YOUR machine. For small recordsets, this is not a problem. For big recordsets, you churn your network hellishly.

If you have split the database into an Access front end and a something-else back end, the query gets run at the server, not on the client (your workstation), so the record transfer is only the solution set for your query, not the whole raw recordset.

2. Access security isn't as robust as security for other systems. The granularity is limited because Access was designed as a small-system relational database manager. One of the central tenets of security is "Know your users." For the size of database suggested by Access, that is possible. For larger user bases, it is not.

By switching to a more robust back end solution, security can be better maintained and/or defined.

3. Access data safety features are not consistently present. In specific, you have to design your own record-level data auditing methods. I.e. if you want an analytic report of every change made to your database, you are going to write that yourself. There is no change-history auditing for standard Access.

By switching to a back end solution that supports field-level change records (audit records), you can over come this problem.

4. Access uses VBA for its code, which isn't necessarily what all IT professionals use. Many like Ada, "pure" SQL, or some C++ variant for direct compilation. Or they use J++, Javascripts, etc. VBA is powerful and pretty much a high-capability language, but it is just not the most popular of all languages among the world of computer programming.

Back end solutions that have more than one data interfacing method can overcome the Access VBA problem.

5. Access has limitations on record sizes, numbers of elements, and a few other structural constraints that some folks don't like - even though most Access databases rarely reach the limits. Some designers just don't like designing in a low-limit environment. Nor can I blame them.

Most of the more robust back end solutions have either no limits in theory or have higher limits than Access. Longer records, more records, more complex joins, more versatile indexes, etc.

Despite all these limitations, there are times when Access is still the best choice for doing some data management tasks. Thinking 'small' is not always bad when the problem is also quite 'small' - but IT professionsal often think big.

In summary, those who don't like Access have good reasons when the database in question is big enough or complex enough to trigger their fears. In that case it would be up to you to assure that they understand WHY Access is still the best choice. If you can't come up with more than just a couple of good but very minor reasons, the odds are that Access ISN'T the best choice in the first place.

There IS something wrong with Access. It is deceptively easy to use. People who have no clue how to gather requirements, design a solution, design a properly structured database schema, write any necessary code and queries, and produce a user manual, look at the Access UI and think, "That's simple. I can do that." And they're off to the races. Creating one disaster after another. They create tables that look like spreadsheets; Do not have any idea that declarative referential integrity exists let alone how to implement it; Their table and column names are filled with embedded spaces and punctuation characters. They do not understand VBA so they use macros for everything. They have no idea what a join is so they rely exclusively on inefficient solutions such as DLookup(). Should I continue? Just read the posts to this and other forums.

The scary thing is that as individual contributors, too many IT people couldn't do much better. As with any organization, IT is divided into groups of people that do certain things. It usually takes a whole team of people to develop a custom application because the development cycle requires different types of expertise at different stages of the project. DBA's don't know how to gather requirements or design a solution. Programmers don't know how to create a correct database schema. Designers don't know how to program.

The development of databases by amateur and inept developers is the real problem with Access. It would never occur to an accountant to write a COBOL or C++ program to solve his problem or to build a DB2 database.

I would think that if your department is committed to supporting itself by developing its own Access databases, it should hire or train a qualified developer and make sure that they have a backup plan should that person leave the department. Of course once he has learned how to do all the things necessary to create solid Access applications, IT will probably want to hire him away because he will have become a Renaissance man or woman (or a dinosaur like me).

I have to agree with Pat - now that I have more experience. I'm not in the IT group, but I think it's safe to say that most "Corporate" IT groups use the same model as described by Pat.

Here's the dilemma: The non-IT folks need a cost effective solution for many "little" things (i.e. database applications for various reasons). The IT group may even have decent Access programmers, but they are forced to charge far more than it's worth in many cases to build a "solid" Access solution - and as soon as requirements are gathered - Access is no longer viable.

Obviously, Access has a place in many companies. It's hard to beat when put in the right hands as far as getting something up and running in a decent timeframe and without breaking the bank.

It seems to me that there is a market for Access programmers to be hired as temps or contractors to quickly and cost effectively create an Access solution without the need to get IT involved. But as Pat said, the skillset required is more than many can handle.

Companies simply don't understand the cost of custom development. The off-the-shelf products you see in stores are sold to hundreds of thousands of people which spreads the development cost so that the product seems affordable. But, when someone sees that he can buy something like QuickBooks for $150, he can't understand why he'd need to spend $40,000 to come close to similar functionality for an Access app. Of course the good news is if he wanted the app developed in something else, the cost would only go up.

Access really is an inexpensive development environment compared to other options but don't make the mistake of underestimate the skillset required for developing solid applications.

If your department does decide to use MS Access faithfully, I would suggest that you offer the most user-friendly designs and development possible; i.e. Switchboards.

All of my Access databases feature user-friendly and resourceful switchboards that include various forms and administrative report command buttons, and not just the plain grey rectangular boxes offered in Access...be creative and use web page style buttons...they are free and really make your switchboards come alive.

Best part of Access Switchboards is that for your users, they wouldn't need to learn too much of the appliaction in order to get the information contained in the database. Feature command buttons to open data entry forms, reports, links to websites, etc...

If you'd like an early sample switchboard of mine, let me know.

I think this entire problem has to do with the difference between people who organize and people who implement. My IT people are not interested in filling out a form to manage information that is flowing through our organization they want to do their job with as little interference as possible. People who develop Access and other databases to keep track of information are data people, I think for most of us it is kind of a turn on to watch as our information systems are built and begin to do the job they were intended for. We know our data like our own children and take it very seriously when even one piece of information is dropped (was it user error or code error). I don't think that IT people care much about development and long term answers, I think they are all about slam it in and move on. I keep trying to tell them that data kept in the correct way is the best insurance that you can have but we all know that if you put garbage in then only garbage can come out.

My advice and $2.50 will get you coffee costing $2.50

Click to expand...