Is MS access the unknown magic bullet of our time?

[Sarameier]

Dear all,


I wish you a happy new year and thank you again for your previous support for myself.

Thanks to all of you, I have managed to get an Access based system running which works efficiently and highly effectively.

Following this, I would like to raise a general point. I hope that you can refute me here (it would make it clearer to me) and that it is the right forum (general question)


For me MS access is the magic bullet of our time. Access allows me to handle all kinds of task, let it be professional or private, without full control and absolutely efficiently. I would not need to invest time into “retrieving” information, and I am just able to use information needed for making decisions.

My question is why has the world been using spreadsheets such as excel, but not a simple database instead, such as access. I am profoundly sad that I did not start using access already in the 90s, but used MS excel instead.

It is all about a few additional functions, particularly

• Using forms and related subforms
• Having the function of a row popping up by click as form in which notes can be entered into a large note field
• Having the function of executing queries, first of all removing objects (rows) which are not relevant anymore by deactivate them

These few functions make me and would have made absolutely happy, and now allow me to manage our daily life which highly depends on “virtual” systems without any hassle.

And no: I don’t think the cloud-based systems are a proper alternative to access. I don’t think that anyone should make herself totally depend on the functionality of a remote external third party.

And no again: I have been trying out many other systems. More simple relational database and more complex (more SQL requiring) database systems. And I think the MS access provides the optimum for the 90 % of computer users.

The make my question more concrete: Why has excel had such an overwhelming success, but access not? Why are so many people using excel but not access?

Since I befriended with access, I use access in most of the cases instead of excel.

 

[theDBguy]

Hi. Congratulations on your success with transitioning from Excel and using Access for your relational database needs. Unfortunately, Excel and Access are two application programs geared for use on two separate intended purposes. Most people find it easier to use Excel over Access because, let's face it, it's not easy to understand relational database theory. And since table data looks very similar to Excel spreadsheets, people tend to think they are the same.

 

[Sun_Force]

I can't understand how it is possible to compare Excel & Access. They are two different apps for two different purpose. One is data organizer, the other is data analyzer.
You will have a nightmare if you try to analyze your data in Access.

From Microsoft on Excel VS Access

In many cases, you can use both programs, employing each for the purpose to which it is best suited. In general, Access is better for managing data: helping you keep it organized, easy to search, and available to multiple simultaneous users. Excel is generally better for analyzing data: performing complex calculations, exploring possible outcomes, and producing high quality charts. If you use Access to store your data and Excel to analyze it, you can gain the benefits of both programs.

there are a lot of possibilities in Excel that's very hard to achieve in Access. Pivot tables, one of them. you can analyze your data just with dropping your field to different sections of a pivot table.

Read under Compare the benefits of each program in following link:

Using Access or Excel to manage your data - Microsoft Support

Learn when to use Access and when to use Excel to manage your data.

support.microsoft.com

 

[Minty]

You can build pivots in access, but it's not as intuitive, or as pretty to look at.

I always use the guide that Access is for storing data and validated data entry and that Excel is a tool for viewing and analysing that data, if you need your end-users to create BI style reporting.

 

[CJ_London]

excel combines data storage and presentation in the same view, whilst access stores data in tables and presents views in forms and reports via queries - so more steps to undertake to achieve a basic objective.

excel does not have datatyping whilst access (all databases) has strong data typing - so access user has to be be more disciplined

excel is more about data analysis/modelling, access more about data processing.

As you have found, applying excel principles to a database is doomed to very poor performance at best or complete failure and worst - leading to the conclusion that 'excel is easier/quicker' or 'access can't do it'. The same goes the other way. You can apply database principles in excel, but it makes for an overly complex and potentially obscure and difficult to maintain application with severe data storage limitations.

Now Word on the other hand - a company I know barely uses excel and databases are on another planet. They 'process' a lot of personal information like name and address - but rather than presenting it on a single row as you would in excel or access, the data is presented as a sheet of blocks - i.e. as you would see on a sheet of address labels. I've shown them how they could complete a task in a minute or two but they prefer their method which typically takes a couple of hours - their argument is it forces a greater attention to detail. They still use enormous amounts of paper!

 

[NauticalGent]

My question is why has the world been using spreadsheets such as excel, but not a simple database instead, such as access. I am profoundly sad that I did not start using access already in the 90s, but used MS excel instead.

Most people find it easier to use Excel over Access because, let's face it, it's not easy to understand relational database theory.

Good question and DBG answered perfectly. I knew a guy who could do brilliant things with Excel - he was not afraid of VBA and could make interactive forms and a whole lot of other "neat" stuff.

Access intimidated him and he would ask me to help him figure things out - in EXCEL! In other words, he would notice what the Access App could do and wanted me to help him do the same in Excel - madness. I would tell him almost daily that if he converted his "app" to Access, a lot of problems he had would go away, first and foremost his inability for multiusers.

He finally gave in and said he would make the leap so I lent him my Access Bible and gave him a link to some great Youtube videos. He lasted about 2 weeks...he simply could not get his head around table structure/normalization "Why do I have to split data up when I can have it all in one place??!?"

No matter how hard I tried to help him over that hurdle, he simply would could not get over it.

 

[Sarameier]

Maybe I did not put my point correctly.

I know the difference between Access and Excel

What I mean is that there is actually (nowadays) NO way to survive without a small relational database.
10 years ago, this was still possible, but in the meantime it is NOT anymore.

And I dont think, relying totally on a cloud system would be wise. But even if this was ok.

I would not see any feasible alternative to ACESS.

Abstractely speaking: I am seeing many people using excel alhtough they are (subconsciously) cravig for relation database function.

I would even go so far that a small relational database should be a human right because it allows to manage our digital nowaday's environment properly. And within the relational database spectrum, I dont see any better alternative than ACCESS

 

[oleronesoftwares]

Abstractely speaking: I am seeing many people using excel alhtough they are (subconsciously) cravig for relation database function.

You have answered your question in one of the posts on the reason in this time/age excel is still popular,
I.E IT HAS BEEN POPULAR OVER THE YEARS

In the IT business, a product might still remain popular as long as the producers are still improving on it.

another reason is that the average human being does not like change, as long as one thing is working, the motivation to improve will be deterred.

another reason is that the knowledge of excel is easy to transfer to another person, so if a staff who created a sheet is leaving the organization, he/she can easily explain to a new staff what the spreadsheet does, even if the outgoing staff did not explain, the new staff can pick it up and understand the workaround of the file.

 

[Pat Hartman]

You will have a nightmare if you try to analyze your data in Access.

Not if you normalize properly. Although pivot tables and charts are quick to use, you can do the same thing with Access, it just takes more effort.

I would not see any feasible alternative to ACESS.

There is none. MS does not market Access to take best advantage of its strengths. For example, the SQL Server team thinks of Access as a rival rather than a complement because they can't separate Access, the RAD tool from Jet/ACE the desktop database engines. If MS pushed Access as a FE to SQL Server, it would make people realize that Access is not just a toy which is the way most large companies view it. Their IT staff wouldn't be caught dead developing an Access app because it is not a "real" development platform and due to the limitations of Jet and ACE, they think that Access can't support more than a couple of concurrent users when the fact is, if your BE is SQL Server or other RDBMS, the Access FE is limited only to the number of seat licenses you have for SQL Server rather than the 255 hard limit or 50ish soft limit of Jet/ACE

And I dont think, relying totally on a cloud system would be wise. But even if this was ok.

I use a cloud backup service for my hard drive but that is not at all the same as trusting my entire company to a cloud solution. More than one of my clients have been burned by cloud solutions. They went in not understanding that there was no way out and that was by design so when they decided that they didn't like the cloud application and couldn't expand on it because it wasn't theirs to change, they had to move on and abandon their data. The best they could do was to export with printed reports and rekey what was critical.

Look what happened to Parlar when AWS decided they didn't like Parlar's politics. Parlar was renting servers from AWS and on those servers, they were running their entire company plus hosting the website that Amazon hated. What most people don't understand is that AWS didn't just take down the website, they cut Parlar off from their ENTIRE infrastructure. Think about it. All your files are gone. Your customer list, your product inventory. Your payroll. Your email. And your provider tells you that you have 24 hours to get whatever you can off the server before it is gone for good!!!!!!!!!!!! That is the jeopardy you are in with the cancel culture.

MS markets Access as easy to use. But it is and it isn't. Excel is pretty obvious so people are comfortable just diving in. Most don't have a clue how to write VBA but they can manage to get data entered and sort it and summarize it which handles most of their simple requirements. But Access is a bigger step up and most people are too intimidated to work it out.

 

[NauticalGent]

MS markets Access as easy to use. But it is and it isn't.

Borland was fond of explaining WHY they named their DB application "Paradox": "Databases are hard, Paradox is easy" - a paradox.

 

[The_Doc_Man]

The reason so many people stayed with Excel is that it is a datasheet right out of the box. I'm 73 now, but when I was a kid in college, there was no such thing as Excel or any of the other fancy spreadsheets. Well, I'll take that back... there were a couple of novel apps that had just recently been designed for people who had BASIC. Limited RAM capacity and a limited variety of functions made them "toys" ... toys that had some promise. Small-business accountants at the time still used analysis pads... a.k.a. paper spreadsheets. The first true spreadsheet program was called VisiCalc and it didn't exist until 1979 - which was four years after I graduated from college with my doctorate degree.

If you ever saw, or ever have a choice to see, the movie Bridge on the River Kwai, there is a relevant scene in which a man was interviewing to join a British WW II commando team. Lt. Joyce was asked about his civilian job. He replied that he was a bank clerk in the role of a "figures checker." They would get massive reports with columns of figures to be added and then passed along to another person to verify the addition, then passed to yet ANOTHER figures checker - the point being that those analysis pads were the only way that business worked in WW II - because computers of ANY size were (a) rare (b) harder than Hell to program because they had no symbolic languages, not even symbolic assembly languages at first (c) expensive to own due to high power and cooling demands (d) almost exclusively a tool for the military. (Look up ENIAC).

Accountants had been brought up with analysis pads as a way of life. Computer spreadsheets were intuitively similar but had the extra added value that they could take sums or averages or other statistical functions of the rows or columns in the sheet. Something so simple that we now take it for granted was NOT AT ALL taken for granted during the time before WW II. So everyone did everything by hand on paper for a while.

Given that people hate change, the introduction of computers to industry after WW II made some people sit up and take notice. But others felt it was just a new-fangled toy that wouldn't last. Now, I think, the skeptics have mostly been convinced.

With thanks to Wikipedia:

The "brain" [computer] may one day come down to our level [of the common people] and help with our income-tax and book-keeping calculations. But this is speculation and there is no sign of it so far.
— British newspaper The Star in a June 1949 news article about the EDSAC computer, long before the era of the personal computers.[10]

But the point is that there was an atmosphere in the world of business to do analysis on a datasheet. Many of the classic textbooks on accounting principles include spreadsheet analysis. We cannot forget that IBM PCs were invented in 1977. (Altair and Apple had competing devices in 1974.) The size of the first PCs was, by our standards, minuscule. Because of size and cost, the PC was at first just another toy. But because spreadsheets turned out to be fairly easy, they came first. (So much for "the chicken vs. the egg.")

Anyway, I digressed into a little history to explain that a lot of folks who studied accounting DIDN'T get exposed to database capabilities so in essence didn't know what they were missing. That is a very big part of why Excel remains more popular among small businesses. You can find a lot of schools that teach accounting but not as many to teach database theory and practice.

 

[GPGeorge]

Not if you normalize properly. Although pivot tables and charts are quick to use, you can do the same thing with Access, it just takes more effort.

There is none. MS does not market Access to take best advantage of its strengths. For example, the SQL Server team thinks of Access as a rival rather than a complement because they can't separate Access, the RAD tool from Jet/ACE the desktop database engines. If MS pushed Access as a FE to SQL Server, it would make people realize that Access is not just a toy which is the way most large companies view it. Their IT staff wouldn't be caught dead developing an Access app because it is not a "real" development platform and due to the limitations of Jet and ACE, they think that Access can't support more than a couple of concurrent users when the fact is, if your BE is SQL Server or other RDBMS, the Access FE is limited only to the number of seat licenses you have for SQL Server rather than the 255 hard limit or 50ish soft limit of Jet/ACE

I use a cloud backup service for my hard drive but that is not at all the same as trusting my entire company to a cloud solution. More than one of my clients have been burned by cloud solutions. They went in not understanding that there was no way out and that was by design so when they decided that they didn't like the cloud application and couldn't expand on it because it wasn't theirs to change, they had to move on and abandon their data. The best they could do was to export with printed reports and rekey what was critical.

Look what happened to Parlar when AWS decided they didn't like Parlar's politics. Parlar was renting servers from AWS and on those servers, they were running their entire company plus hosting the website that Amazon hated. What most people don't understand is that AWS didn't just take down the website, they cut Parlar off from their ENTIRE infrastructure. Think about it. All your files are gone. Your customer list, your product inventory. Your payroll. Your email. And your provider tells you that you have 24 hours to get whatever you can off the server before it is gone for good!!!!!!!!!!!! That is the jeopardy you are in with the cancel culture.

MS markets Access as easy to use. But it is and it isn't. Excel is pretty obvious so people are comfortable just diving in. Most don't have a clue how to write VBA but they can manage to get data entered and sort it and summarize it which handles most of their simple requirements. But Access is a bigger step up and most people are too intimidated to work it out.

When MS announced the launch of the Dataverse connector for Access, the presenters prominently referred to Access as the "original low-code solution" or words to that effect, thereby alienating most of the very audience they were addressing, IMO.

I had him do a follow up presentation to my User Group chapter, in which he addressed both the original assertion and the feedback he'd received in the interim. And that doesn't even account for the problem of migrating data from a spreadsheet to a properly normalized relational database design.

It is clear to me that at the higher levels of management, Microsoft doesn't quite understand what they have in Access. And therefore, they don't appreciate it enough. The problem, though, IMO, is compounded by the fact that Access makes it possible to create "spreadsheet style" tables and work with them with low-code (i.e. macro) commands. Far too many people never get beyond that stage, and that includes the so-called IT admins who, however professional they are in their own roles, don't understand Access either.

 

[Pat Hartman]

In the spring of 2006, I was invited to Redmond to a presentation of Access 2007 by the Access team . It was a very exciting three days. The following week there was a big event at the Microsoft office in Manhattan to I went there also. Over hors d'ovures, I was chatting with the regional SQL Server sales manager who informed me that Access was dead. I explained my trip to Redmond and told him that thee were exciting changes coming. He insisted Access was dead. It was almost a fist fight. But I remained calm and got more information. Turns out that because for 2007, the Access team was taking a copy of Jet and turning it into ACE so that Access team would no longer be dependent on the SQL Server team to manage their database engine and since Jet would no longer be used by Access, he insisted Access was dead and he was happy because he thought "Access" was a competitor. He didn't understand at all what Access is and that "Access" is NOT a competitor to SQL Server, it is a complement and should be marketed as such.

 

[jdraw]

To summarize Pat and GPG:
2022 -2006 ~~ 15+yrs----It is clear to me us that at the higher levels of management, Microsoft doesn't quite understand what they have in Access.

 

[Pat Hartman]

And it is MS itself, particularly the SQL Server team that starts the rumors about the "death" of Access. There was the period around 2K where the next version of Access was going to ship with a desktop version of SQL Server in addition to Jet and so Access is dead said the SQL Server team. Then there was the dropping of support for Projects and the dropping of the various iterations of Web pages.

 

[Isaac]

Dear all,


I wish you a happy new year and thank you again for your previous support for myself.

Thanks to all of you, I have managed to get an Access based system running which works efficiently and highly effectively.

Following this, I would like to raise a general point. I hope that you can refute me here (it would make it clearer to me) and that it is the right forum (general question)


For me MS access is the magic bullet of our time. Access allows me to handle all kinds of task, let it be professional or private, without full control and absolutely efficiently. I would not need to invest time into “retrieving” information, and I am just able to use information needed for making decisions.

My question is why has the world been using spreadsheets such as excel, but not a simple database instead, such as access. I am profoundly sad that I did not start using access already in the 90s, but used MS excel instead.

It is all about a few additional functions, particularly

• Using forms and related subforms
• Having the function of a row popping up by click as form in which notes can be entered into a large note field
• Having the function of executing queries, first of all removing objects (rows) which are not relevant anymore by deactivate them

These few functions make me and would have made absolutely happy, and now allow me to manage our daily life which highly depends on “virtual” systems without any hassle.

And no: I don’t think the cloud-based systems are a proper alternative to access. I don’t think that anyone should make herself totally depend on the functionality of a remote external third party.

And no again: I have been trying out many other systems. More simple relational database and more complex (more SQL requiring) database systems. And I think the MS access provides the optimum for the 90 % of computer users.

The make my question more concrete: Why has excel had such an overwhelming success, but access not? Why are so many people using excel but not access?

Since I befriended with access, I use access in most of the cases instead of excel.

Can't compare Apples and Oranges..........although to your point, Yes, many people use excel "as if it were" a database = bad.
Because people like familiarity, and new things are sometimes frightening - to all of us.
Access requires more understanding, and new concepts.

I'm glad you like Access, have fun!

 

[4mal]

As databases go...Access is pretty limited in comparison to say My SQL or SQL Server but in my current gig as a contractor supporting a US Gov't agency with no budget for tool but having MS 365 licenses for all. I'm making do. (I am sooo missing stored procedures and C#.)

IMO the proper way to see Access, Excel, Power BI, Power Point and even MS- Forms... is that they are complimentary parts.

With properly implemented ETL, Data Validation rules and Data integrity checking - much of which is implemented in VBA - Access is the system of record/ source of truth.

Excel pulls from the source of truth for analysis that would be less fun in Access. Problem is the spread-mart is subject to adverse manipulation, lack of version controls, going style after the next round of ETL... So Excel (to me) is strictly an in the moment, spot analyzer. Often that analysis is then published to the PowerBI environment with a date and time stamp.

Power BI obviously allows for user interaction with the data as allowed by the dashboard developer. So as we all know, PBI is the presentation layer in thr dynamic environment.

Power Point is of course the static presentation layer for management reporting. The data can't be manipulated and it well constructed, mis-interpretation is at least difficult, even for managers being bombarded with a gazillion other things and the resultant attention span of a gnat.

MS- Forms can serve as a 1 way, field back to HQ communications tool. It is easy, stupid easy.. and limited. I have it collecting a few bits of field data from trusted sources am an now in the process of adapting the resultant data, exported to Excel into my data validation and integrity checking rules prior to ETL into the source of truth. It isn't even workflow-lite. It is no cost, low code and simple though and that given my lack of clout and budget makes it useful in its own limited way.

I'm pretty sure this is all old news to you folks but I hadn't heard it expressed above and maybe this will be of use to folks like the OP who are beginning to embrace the database side of things.


I was going to include a link to the Wikipedia entry on Spreadmart but that doesn't appear to sail here so Google is your friend ...

I have used the above Wikipedia reference a few times over my career to help upper level managers grasp that they rely on spreadsheets at their peril... maybe it will be useful to someone else too - or maybe it's just old news. I'm a newbie here. First post and hopefully not bending the cultural needle around the pin stop ;-)

 

[theDBguy]

Hi @4mal. Welcome to AWF!

 

[CJ_London]

Access is pretty limited in comparison to say My SQL or SQL Server

don't think anyone would disagree with that - but access is more about front end development, not the back end. You can of course use sql server express which is free as an alternative to the ACE db engine that comes with access

 

[4mal]

don't think anyone would disagree with that - but access is more about front end development, not the back end. You can of course use sql server express which is free as an alternative to the ACE db engine that comes with access

In this environment... only in my dreams ;-) But yes. I implemented a FRACAS that way.