What are the appropriate uses for MS Access?

Question

It's the Office app everyone loves to hate ;)

I admit that it's often abused or misused, but it does come in handy on occasion. So what do you think? Are there appropriate uses for Access, or should MS jetison it from the Office suite?

Answer 1

I use Access for analysis of large amounts of data that can't be imported into Excel. I also find it's easier to analyze the data with SQL queries than it is with Excel formulas and/or VBA macros.

Answer 2

Let's face it. There are a lot of people who are not developers or "tech" folks who need to store data and MS Access provides a solution for them. IMO it's a great way to store data for smaller applications / demands or the casual computer user.

Obviously it can be used to a greater extent as many bigger businesses still like to use it as a backend.

Answer 3

I don't think it should be jettisoned from the office suite because there are plenty of uses for a database application and not everyone needs a full blown instance of SQL Server or even an instance of a freebie like MySQL.

Answer 4

I think Access is somewhat different from other Office apps in a way that to be somehow productive with it you should know a lot of things, i.e. database basics.

In my limited experience I've never met a person who really knows how to use Word or Excel effectively, who knows how to use every obscure feature. So most people use Office when they need something quick and dirty but decent-looking.

Answer 5

Prototyping is probably the #1 use of Access for a serious developer. It's much quicker to get things done in a desktop sandbox that I can keep around for testing later.

It's also useful as a front-end for other database engines. If you know how to use the Access query wizard, it can be a great productivity tool.

Answer 6

I found it handy as a fat-client viewing - for example - an Oracle database. Of course you are limited to the ODBC-driver you are using. But I would not use it for a "serious" application - for my personal cd/dvd/blu-ray collection database it is okay and easy to use - for everything else I would use a real database.

Answer 7

I use MS Access as a way for my clients to pass me large data..

then i just import the data to my SQL Server its easy for them and easy for me..

it works. =)

Answer 8

It's ability to access and query data from a range of sources can be really handy. You can link to text files, Sql Server and Oracle etc. at the same time which can be really helpful when doing data conversions and the like.

Answer 9

First off, let's make sure the question is clear:

1. "Jet" is the default database engine for Access (in Access 2007, it's the ACE, which is just an upgrade of Jet 4).

2. "Access" means the database front-end development tool.

Jet is a perfectly suitable data store for data that is used by individuals and small workgroups. Once user populations approach 20 and security/access controls needs grow, Jet is less satisfactory as a data store.

Access is an excellent RAD database development tool. It is full-featured and offers access to almost every back-end data store in existence. Professional developers can deliver a working Access database application in much less time than any of the other alternatives.

I have been developing professionally in Access since 1996. The vast majority of the clients for whom I've worked would not have been able to afford a database application built on any other platform (this is irrespective of whether Jet is used as the back end or a server database), because they are mostly small businesses with minimal budgets and little patience for extended development processes. Hiring an outside developer to produce an Access app is the only way they are going to be able to afford any significant database application.

I have also been brought in by larger companies to do "micro development" for individuals and departments, as well as to rescue user-developed Access apps that have become critical to business needs. Access provides an excellent path for these user-developed applications to be turned into robust applications, precisely because of it's back-end flexibility, but also because front-end development is so quick that it's fairly easy for an experienced developer to analyze an existing app and figure out how to revise it to work more efficiently and reliably.

The RAD development aspect of Access is something that I think most of the posters on StackOverflow have really never encountered. The vast majority of questions tagged MS Access do not involve Access at all -- they are all about using the Jet database engine. This confusion is not the fault of users, because MS has done a terrible job in its documentation of confusing the situation (not least with naming the Jet ODBC and OLEDB providers "MS Access") by using "Access" when they mean Jet (or ACE in Access 2007).

Many people from outside the Microsoft orbit seem to me to not even understand that Access is a lot more than just the Jet database engine. This makes it hard for professional Access developers like me to be taken seriously.

But I can do without that respect as long as I keep getting calls to develop and revise Access applications.

Answer 10

Access is great for a few things.

1) Simple Databases with fewer records (read comments) that don't need weblication. My experience used to be 10,000 records, then it was maybe 100,000. With the gains in computing power and any changes to the software it self it could be higher.

2) Building a simple proof-of-concept application in an evening to show someone the workflow it needs to be

3) Data importing/analysis. Sometimes I have to compare excel files, with lotus notes databases (via NotesSQL). Access lets you connect to a bunch of odbc tables and compare them all in one DB. Very handy. Also the built in reporting isn't bad.

4) I know a developer who uses it as a multi-user front end to MySQL. He does really well with it.

5) Fleshing out databases / relationships during a meeting or a discussion. Some things Microsoft does well.

6) When you have to build a project for a client, and they need to start using something TODAY. Get them a structure DB that you'll use later, build a GUI in 2 days, get them going bare-bones while you build the real app. When time comes to switch, you have some data to test, a tested data structure to run the real solution with.

7) Access is often used in companies, especially larger ones that can't get budget approval to solve an application need. Often someone will discover access and build something internally. Then it will become valued and someone needs to be hired to continue it, convert it, etc.

Answer 11

There's no question of MSFT jettisoning MS Access altogether: it makes them good money for the little maintenance they put in (after not touching the engine for nearly a decade, the changes for Access 2007, including DAO, were very disappointing).

I think the question is: should MS Access be disassociated from the Office suite because it is so 'different' for the other? Although it is undoubtedly a niche product, perhaps comparable to Visual FoxPro, I imagine MSFT have a good idea of the commercial benefits (or otherwise) of the MS Access application being part of a suite and I think MS Access will remain in the party for many releases to come.

Answer 12

In a phrase "local processing". When data is local you can stop worrying about a long list of problems-- locking, establishing a fast and reliable network connection, setting up a server side reporting tool, etc. Local data is also static. You could accomplish this with a server side reporting database, but who has the time.

Answer 13

Access comes to be appropriate as soon as Excel cannot do the job anymore!

This can be true at an individual level. It also works (with some restrictions) at the enterprise level. In this last situation, Access will be basically used to build front-end apps meant to work with back-end database servers, not mdb files. Use of Access as an enterprise software has been thoroughly debated here, but don't believe that anybody who 'knows' Access knows enough to build a professional software out of it.

Answer 14

I used Access for a fast "application" for telemarketing. It was fast and easy and worked. Also a very graphic way of learning the different relationships between tables, queries, keys and some things a 16 years old wouldn't understand by reading a "Learn MySQL in 25 days". Maybe if some rules or advices where given during the development process, and explain Access limitations upon start, would prevent from over-using it. Anyway, look at the examples Access comes with.

Answer 15

You can get a lot of functionality in a short amount of time. Many IT pros want to discourage its use by the less technical people. You can't expect everyone to wait for the robust, scalable, secure, REAL application to be created by the over-loaded IT department. I'm reminded of the Monty Python skit where they hunt for mosquitoes with a bazooka.

Answer 16

In my company, we have running apps on MS SQL and Oracle servers.

But to be honest - "normal" people don't know about anything besides "If I want to close this application, I choose File/Exit" and alike. That is why we created lots of MS Access databases + Excel workbooks. Each one is backed by VBA, and is usually used to grab / view specific data, which aren't easily accessible in other way.

Each MS Access database has usually one main form with two, rarely more, buttons. We hide everything besides this form, it is so easy to use, that everyone is able to do so, without any training.

Answer 17

MS Access is a great tool for:

1) database development and prototyping - once you develop your prototype you can always use it to copy all the functionality with the other set of tools/technologies/languages. The typical evolution path for MS Access is: - design database schema with all the realtions - thanks to the great GUI it's like creating ERD - design forms to show, update and input data - because it's really easy to make any changes you can quickly consult all the changes with your clients or department colleagues. Of course, after discussion you can quickly implement all the proposed changes. This works very well for small clients (SMBs) and internal, department level development. In these cases you can forget about serious (written) requirements specification, design documents, any agreements or contracts... There is just a work to be done, people require it and in most cases you're the only developer available. - design reports quickly according to the lousy specified requirements like: "I would like to see a table with numbered rows, if it is possible, and each row should contain the following information, on all the rows where the value of this and this is more than the sum of this one I would like to have a kind of mark or maybe a dark background to see what is the most important, and, I would forgot, it's very important to sum all these fields at the end, and is it possible to add a chart here? I have some charts already developed in my personal MS Excel spreadsheet I've created several for my own purposes...". It looks like that very often and what is very important - MS Access is a great tool in such situations. Not only you have a working, easy to use and made any changes, reporting functionality in MS Access, but also you can experiment with the ideas presented by your users, customers, colleagues. - split your MS Access solution into two parts: database frontend (forms, reports, SQL queries, VBA code etc.) - let's say an application or fat-client (MDB file for development and MDE file for deployment and daily usage) and database backend (all the data stored in a dedicated MDB file somewhere on a network share). - start using the first version of your solution and test it very deeply in daily usage. It has to be split at this stage (frontend/backend). - as it becomes more popular and the data is growing it's also a very good idea to move all your database backend from MS Jet/ACE (tables, relations and data) to the more advanced database engine. You can choose from the variety of them (or even all popular), including: MS SQL (of course), Oracle, MySQL, Postgress etc. This process is very easy and can help you scale the whole solution very efficiently. Of course, you'll have to use ODBC connections, but to be honest, it's very good for client-server database applications. Moreover, you can then implement even more interesting features for your solution by using stored procedures and all the features of the database engine of your choice (including PL/SQL stored procedures in Oracle, Java based stored procedures, security features etc. - you name it ;-). All the things to remember is to take care about column types definition (double check these are correct after automatic conversion of your schema) and then to transfer all the data - for most database engines you have dedicated migration tools (you can use SQL Developer for Oracle or very good, also freely available set of tools for MySQL. In case of MS SQL Server this process is almost logical step and you have everything you need built-in into MS Access). - after such move you can still use the same frontend for years. Of course, if there is a need and you have time/resources you can copy your frontend functionality in anything you want. It can be MS Visual C# - you'll be able to use more advanced GUI controls even, so maybe it's not a bad idea, but in most cases I've seen there is no need to exchange MS Access based frontend with something else (once it's properly written).

2) data analysis - you can work with external database, get the data you need, store everything in your local copy, develop SQL queries (by hand or using wizard), create reports, export data to MS Excel for further processing and then paste everything to the final report written in MS Word. From this point of view this is the logical consequence to sell MS Access as a part of MS Office. Please not forget that you can also buy MS Access separately and it's not too expensive - for it's price this is very interesting and "must have" database development tool for all Microsoft-related IT Pros.

Now to discuss with some popular myths: - MS Access is not stable - WRONG! MS Access is very stable, flexible and quite scalable (see above explanations) - it's not true that it's easy to corrupt anything using VBA. It's ridiculous argument. If you know how to code in VBA it's not possible. I've found that people are coding without any vision and with the improper assumption that "VBA is so easy that everyone can code in it". This is not true. You have to understand VBA the same way you have to understand any other technology or language. Moreover, people are making simple errors very often, then they're not compiling the source or ignoring the errors, then they use MDB file for they everyday work (which is a kind of absurd) and complain that MS Access is not stable. Is it? They should appreciate it at least was able to start the application and is showing them error messages. You cannot ignore any error message and then require the application to be stable - it's not MS Access fault in most cases.

• MS Access is a toy - WRONG! People don't understand the difference between Access and Jet - both Office users and developers. Once again, even the most serious developer should spend some time on learning MS Access to understand what and how should be done properly. It's not a toy! It has it's own logic.

• MS Access cannot be deployed easily - WRONG! Once you're not generating a mess in your frontend by using .NET based controls, a lot of OCX which have to be registered in your system it's not so bad. I would say it's very comparable to an application written for .NET. You also have to take care that the proper version of runtime environment is installed on each desktop (in this case I mean MS Access Runtime Environment which is freely accessible from Microsoft website). You can also use 3rd party tools to prepare installation wizards.

• MS Access cannot be effectively used by professional developers - WRONG! It can be used very effectively, although there is a requirement, that this developers understand MS Access and know for what purposes it should be used.

• MS Access is to hard for casual computer users - WRONG! It can be and it is... See how many analyst are using MS Access for data processing?

• MS Access is good only for data import/export from/to another professional database engine like Oracle - WRONG! It is good for such purposes, but not only for them. Once again - misunderstanding of the MS Access as a tool.

• MS Access is all about databases, frontends and working with data - CORRECT! You can perform all the tasks related with databases and data. You don't have to use MS Jet/ACE engine, once you decide to use more powerful database engine (Oracle, MS SQL, MySQL?). Anything ODBC enabled is supported. The only limitation can be a small set of attractive GUI components, but these are flexible enough in most cases (yet very simple in fact - and it required some creative thinking to develop easy to use GUIs).

• MS Access killed MS Visual Basic 6.0 - NOT SURE! To some level this might be true. Many people were developing database frontends in MS VB 6.0 - seems that MS Access is much better choice in most cases. Although, one cannot expect that he would be able to develop the applications he was creating with the MS VB 6.0 - I mean simple games, OCX components, DLL libraries etc. Once again - two different tools dedicated to two different purposes. Partially, some of the functionalities were similar - this is were MS Access won, I mean database development. The rest of possible MS VB 6.0 usages are covered by different tools right now (and they were before) - MS Visual C#, MS Visual Basic .NET or pure MS Visual C++ (the best tool for system level programming, including OCX and DLLs development).

• MS Access cannot be extended with the additional functionalities - WRONG! If we are talking about MS Access itself this statement is a nonsense. If we are talking about MS Access applications - it's not true. You can develop your own OCX components or DLL libraries and use them freely in you application (from VBA code). This helps you to create some GUI enhancements as well as add non-standard logic to the application (like sending output to the serial port, sending emails, controlling external devices, reading data from required ports for any purposes - like barcode reader support).

• you cannot use web-services - WRONG! Of course you can.

• MS Access in not ready for enterprises - WRONG! It is. You can even easily integrate it with the MS SharePoint. This feature is expected to be really nice in MS Access 2010.

• you cannot develop web-based applications - PARTIALY TRUE! Alright. In 2003 version all these MS Access Data Pages were not too nice, but at least you have them. All in all it was always a choice to develop you frontend in different technology, like PHP or ASP.NET - it suits most of the web needs much better than dabbling with MS Access Data Pages. This was the reason MS removed them from MS Access 2007 as I think. It seems that in MS Access 2010 we will have some new web-based features which can improve this field of MS Access development. To be honest - for web based applications MS Access was definitely very poor if we are talking about version 2003 and with version 2007 it was not possible to make any web based application. This is the weak side of MS Access, but we have to wait for version 2010. Still, we can consider other technologies for web based frontend development. We should migrate away in such scenario - I mean to write the frontend in PHP or ASP.NET and to move all the backend from MS Jet/ACE to MySQL or MSSQL Server (or anything other). This is not the place where MS Access suits the best.

In conclusion I would like to add that I was working with Oracle Forms and Reports and these tools are much more complicated than MS Access. Of course, there are some advantages of these solutions also, but still I can find MS Access more suitable for the vast majority of tasks I'm supposed to do in my professional work... Not to mention license costs of MS Access and Oracle tools - MS Access is much cheaper. Moreover, you can download MS Access 2007 Runtime for free and use it to distribute your MS Access based solution. It's now possible to develop an application in MS Access for your clients who don't want to buy any additional license - correct me if I'm wrong! They will just pay for your work and use it in this runtime - it look quite nice. For most users it's not too important how something looks, but it has to be intuitive, responsive and offer all the required functionality at the first place.

Best Regards,

Pawel Wawrzyniak

http://dcserwis.pl/

Answer 18

MS Access is the answer to large IT bloat. If I want a tracking/reporting database for any team generated data I have to jump through a ton of hoops and sometimes doesn't get done because we don't have enough money in the budget for a "pro" database guy/gal do it in Oracle or MS SQL Server. I take one afternoon with Access and maybe even some VBA code and I have something that will get the job done for what we need it for. There is why Access will not go away anytime soon

Answer 19

http://www.fmsinc.com/MicrosoftAccess/Strategy/index.asp

Answer 20

MS Access is a great tool for any number of applications. The greatest problem with MS Access is developers that don't know what they are doing and then blame the tool.

Access is an outstanding tool to rapidly create and deploy a db application in record time. It integrates both the back-end and the front-end GUI into one complete package! It can seamlessly be automated to work with a multitude of other softwares including the entire MS Office Suite (Excel, Word, Outlook, PowerPoint,...).

There are a couple of limitation that one should keep in mind.

• It does not work over wireless connections (It is great dependent on Network stability) and should only be used on a LAN (not a WAN)
• It is not a web database (was never meant to be)

That said in a Networked LAN environment it is fast and dependable!

IMHO, it is the fastest development tool from start to finish. And since Access 2007 it can even be distributed to end-users with the run-time version for FREE!!!

It is a great step forward for any business which uses Excel to do data tracking as it incorporates automated reporting and the form permit data validation and so much more. Often I see people sold system that far exceed there needs. Access fills a huge gap between tools such as Excel and Industrial Database Systems such as Oracle and is used all over the place in companies such as, Bombardier Aerospace, Boeing, CDI, and many, many, many, many more.

It should be part of any good developer's tool box, but sadly it has been mocked by those that do not truly know how to use it.

Just my 2 cents worth.

Daniel Pineault

CARDA Consultants Inc.

Answer 21

Very valid precision David! But for the average user, ie: entire db developed using standard ms access forms... this is a limitation that should be noted.

Daniel Pineault

CARDA Consultants Inc.

Answer 22

Access has to be the most misunderstood product in the history of software.

Never, in all the history of software, can so much be done with so little. That is Access, in a churchillian nutshell.

There is a degree of snobbery in regards to Access. Like all snobbery, it is all based on ignorance.

to put the record straight, Access is a Holy Trinity.

1. It provides a software development platform with a mature (albeit a bit outdated) language such as VBA, that allows you to design forms and reports (it is one of the best reporting engines you can get). Despite its age, VBA is an incredibly practical language that allows you to do pretty much everything you need. Maybe not quite as elegantly as in other languages, but still with a kind of brutish simplicity. A club with still kill you as effectively as an atom bomb.

2. It provides a visual interface for users to work with a "database engine" (JET or ACE depending on the version, ACE as of 2007+).

3. It is a DBMS for JET or ACE.

Then you have the files that the application works with. These files can be "databases" or they can be software. As software files, they can be either full blown apps, extremely effective and easy to maintain business software that just runs and runs. Or it can be a library of software (see my article: Using Database Library Files in Your Access Application

The files can also run small utilities much like VBScripts.

And they can also act as database files, storing data. This is perhaps their weakest point as they do not afford the same degree of security and performance as other, industrial strength products such as SQL Server. But then it is an Office product. It is not competing with SQL Server for enterprise level apps that have cost 100K to develop.

However, there are a number of products such as MUST that allow you to develop quickly in Access, then upgrade to SQL Server.

Yet another string to the Access bow is the products ability to speak to a number of different data stores, such as SharePoint and SQL Server, and to use these as its "Back End".

Now, it even has Web Databases, whatever they are. OK, they are "forms" that allow an Access Developer to quickly expose to the web, with a minimum of skills. It remains to be seen if these are a good idea.

So all in all, next time you want to knock Access, why not take the time to get to know it? Otherwise, you are just shooting your mouth off and all you achieve is to show just how ignorant you are.