Why use Access?

[spikepl]

I am too deep in this to make sense to myself, and need clear (to non-developers) and concise responses to questions like:

"Access? What the heck is that? I just want to expand the functionality of my spreadsheet, so why bother with some MS product I've not even heard of? Or pay someone to make a ... what ? A database? "

Many pages extolling the virtues of Access preach to the converted, using the insider speek of "relational database" and so on, which makes little sense to the mom-and-pop operation that has happily been struggling along with some spreadsheets for years, and has perhaps even built a pretty complex spreadsheet to support the entire value chain.

Do you have some good arguments then please add them here - I reckon most subsequent readers could use such texts to their own benefit.

Many posters here are people who use Excel or perhaps not even that, yet somehow find their way into Access. Why?

 

[spikepl]

At last managed to find a reasonable summary:

http://www.opengatesw.net/ms-access-tutorials/What-Is-Microsoft-Access-Used-For.htm

 

[essaytee]

I think Access (or any database system) will always require a little more brain effort than a spreadsheet. In this regard I understand why spreadsheets are pushed to the limits and beyond.

 

[smig]

As I use Access for the last 20 years I can't even understand the question

 

[spikepl]

Here is another link useful for summarizing the arguments: http://www.makeuseof.com/tag/excel-vs-access-can-spreadsheet-replace-database/

 

[gemma-the-husky]

To me, the effective differences between Access (Database) and Excel (spreadsheet) for a spreadsheet user is that

a) it strongly types each column of a spreadsheet, and thereby prevents many inadvertent errors that arise in spreadsheets

b) it provides an enormous increase in both processing speed and processing power

c) a database avoids the need for multiple spreadsheets covering multiple date ranges or departments. Generally you have one database managing all your data


However

d) because of this you hardly ever see all of the data in a database - you just see a filtered view, and therefore you need to be sure that the database design manages the data correctly, so you can trust the part you do see.

e) The fundamental (but unapparent) difference is that a database has no direct concept of a previous or next row, so much of what users are used to doing with spreadsheets needs to be re-learned. This principle is so very fundamental. Each row of an access data table is to be regarded as a set of data in its own right. Unlike Excel where you can sort a column independently of the other columns, this just is not possible in Access. Sorting a single column differently automatically results in all the other data rows being reorganised accordingly.

f) and unlike a spreadsheet where generally a user can design a decent spreadsheet without needing macros or code, this just isn't possible in Access.

g) therefore designing a database requires a level of skill and understanding that is far higher than the level of skill required to design a spreadsheet.

h) but the end result is something far more coherent than you are ever likely to achieve with a spreadsheet.

 

[Pslice]

Access should be used when you need to have consistency and control over data entry into a relational database, spreadsheet programs like Excel would take a lot more work to have that type of setup.

 

[Minty]

I think that if you try and apply any sort of scale/size to a spreadsheet and then add multi-user into the mix you have a ready answer.

Spreadsheets cannot handle multi-user updates with any reliability.
They are a very capable and sophisticated reporting tool, not a place to store data.
Add in back-up plans strategy etc...

 

[smig]

A spreadsheet is just a spreadsheet, not a db PERIOD

 

[Timo van Esch]

If I may be so bold as to answer this question: I had a dream...

No, seriously. In 2011 I was working at the head-office of some big company and they were mainly struggling along with all those nifty spreadsheets. After 2 months, they started to see that what they did with Excel, I could do in 1/4 (or less) of their time in Access. Of course they don't like that, because it endangers their carefully built and shaped knowledge...

Now, on to that dream. Much more interesting.
Within the stress of a new job, on highest-level management support, and being overwhelmed with the insights of difference between Access and Excel, I literally dreamed of playing around with the Excel spreadsheets in the air, like in the Matrix, or something.

By the time I started to relate them and make any sense out of the data, in a 3-dimensional focus, it became an Access database. Since then I explain people that Access is basically just a 3D version of Excel, where you can not only relate A1 with X227, but also directly with the Y- and Z- axle, as in with the sheets behind it, filling new tables on the fly.

For many it's an eye-opener :-D

 

[gemma-the-husky]

timo

it's a very good analogy, but access is actually even more flexible than that, as I am sure you realise. Rather than having a "fixed" stack of spreadsheets, the spreadsheets themselves are completely flexible. You can consider the stack as a stack of 12 monthly sheets, or 52 weekly sheets. And you can amend the columns that are included in each spreadsheet in the stack.

And the other great benefit is that the columns themselves are strongly typed, so you are protected from entering "junk" data into the database, in a way that excel can't give you.

 

[Timo van Esch]

Hi Gemma,

I totally agree, of course.
It is the flexibility that makes it 3D; you can manipulate on all levels, instead of getting stuck in 2 dimensions...

 

[spikepl]

A number of good points about the difference between spreadsheets and a Db raised by Dave, a.k.a. gemma-the-husky, http://www.access-programmers.co.uk/forums/showpost.php?p=1411341&postcount=8

 

[spikepl]

Some more pros and cons of Access relative the traditional s/w: http://www.access-programmers.co.uk/forums/showthread.php?p=1434340

 

[The_Doc_Man]

Here we go again... I'll play around with the answer, taken from my own warped perspectives.

First, you never need Access for anything at work. You can do it all with flat text files and maybe a couple spreadsheets. In fact, most small projects start this way. You make a couple of files and a couple of spreadsheets in your copious free time. The problem is that Nature abhors a vacuum, so your projects grow until they suck up all your copious free time.

The only time you need Access is if you HAVE all those flat-files and spreadsheets, your project just doubled (or worse) in size, your customers are clamoring for new reports, your boss is talking deadlines, and somewhere in there you have this silly idea that you should have a life of your own. Your wife (if she is still married to you by this time) is clamoring for a vacation and your most recent child doesn't resemble you - but then, you were so busy with your work project that you can't remember when you and your wife last got together on that little personal project.

The biggest problem with starting with or switching to Access is that if you are doing something significant, there is a big up-front (design) cost. The biggest benefit with Access is that once you have your data laid out correctly, everything else you might need to do is so much faster that you actually CAN have a life of your own.

Why do you need Access? Because projects NEVER shrink. They NEVER stay at the same level for long. They ALWAYS grow in size and in requirements. But the capacity to grow projects using static, limited facilities with no flexibility means that all the work of growing the project is in your head (and is the smelly monkey on your back). You need Access because it can grow bigger very easily. You need Access because it can out-analyze a spreadsheet or a flat file. You need Access because it provides a path for you to grow that project into an SQL Server back-end and manage huge piles of data. Your projects aren't static - and Access can grow with you.

Plus it looks nice on your resume when you finally burn out in that company and look elsewhere for a new job.

Told you my viewpoint was a bit warped, didn't I?

 

[gemma-the-husky]

Another view. IMO

You don't NEED access (or a database), only in the same way you don't NEED the internet, or a mobile phone. If you are happy to struggle with out of date technology, you are fine without databases.

In truth, corporates do NEED databases. They need proper well thuoght out and well structured databases. The hard part is they need to replace a lot of their spreadsheets with databases, which isn't easy.

The real benefit you get with access is that you only need one data file.

what you often see in companies is folders full of weekly data summaries, with a template ready for the next week, a load of cut and pasting to manage the data, and the irritation of using a tool that cannot be shared simultaneously by multiple users.

put them all into access, and all of a sudden you lose this awkward structure. You need just one set of rules, which once established protect the integrity of the data. you can control all your data with a single structure. You can report easily on any date, departmental, or other range you want.

 

[CJ_London]

what you often see in companies is folders full of weekly data summaries

totally agree - and then some whizz, without realizing it accidentally moves a folder into another folder (or moves rather than copies a file) and bingo data gone. And lets not mention variations in spreadsheet layout and naming conventions. A client required me to summarise some 10,000 spreadsheets (one per customer per month going back years). When I finally finished there were nearly 1300 variations of spreadsheet layout and I know not how many different naming conventions

 

[spikepl]

Another sad true-life story:

I got a potential customer drowning in spreadsheets undergoing a major spreadsheet revamp (the put-lipstick-on-a-pig sort of thing) throughout all departments. They did consider Access, but "we've got no one to support it" killed the idea, even before I could counter with some no-way-could-you-refuse-this support deal. :- (

The SME has an IT-department!!