Is MS access the unknown magic bullet of our time?

[Pat Hartman]

As databases go...Access is pretty limited in comparison to say My SQL or SQL Server

Welcome aboard but you are making the typical uninformed non-Access user mistake. You don't actually understand what Access is.

Access is NOT a database. PERIOD. MySQL and SQL Server can't do anything that Access can do is the proper way to look at it since they can't develop applications but Access can. They are database engines, not application development tools. Access is an application development tool. In fact it is a RAD tool. It is NOT a database. Jet and ACE are databases. They are designed to work on a desktop or a LAN. When Access is connected to a Jet or ACE BE, Access is constrained by the limits of that BE. i.e. a physical max of 255 users but a practical max of ~ 50 concurrent users. However, when Access is connected to a SQL Server BE, it is constrained instead by the number of seat licenses you have for the server so if you have thousands of seat licenses, you can have thousands of concurrent Access users. So, Access is as expandable as you want. The weakness of Access is that it does not easily support multiple simultaneous developers so perforce, applications built with Access rarely get very large (I'm not talking about the size of the tables). However, over the course of years, you can build anything so there are actually lots of huge applications written in Access. They were just created one feature at a time by a single developer or a series of developers over time.

The Access/Jet/ACE synergy is confusing and way too many people confuse Access with a database engine. They do not understand Access' connection and dependence on Jet/ACE. Access objects are stored in a Jet (.mdb) or ACE (.accdb) database container so Access cannot survive without Jet or ACE. However Access is not dependent on Jet or ACE for its data storage needs. Access can work with ANY RDBMS that supports ODBC. It even works with custom ODBC drivers such as the one for QuickBooks to produce add on functionality for QuickBooks. Jet/ACE have a stand alone exe and can be and are frequently distributed separately. They too can be used by any application that is ODBC compliant. Access however provides a GUI that works like SSMS for SQL Server. You use Access to create/manage Jet/ACE objects using a GUI if you don't want to use DDL or DAO/ADO code.

Access is not a general purpose tool. It is intended to be used to build data-centric applications that work on a LAN. You would never use it to build a graphic intensive game or a channel program (low level code that connects the operating system to various devices) If you need something else, you should find a different tool. Since Access is a RAD tool, it does lots of stuff for you which is very helpful to people who have no development experience. For people who do have development experience, Access is a real challenge because they keep trying to make it work the way they think it should work instead of adjusting their attitude to go with the flow. Let Access be Access and it will be easy and extremely fast as a development tool. Or you can fight with it and spend your life frustrated. Since I came to Access with 30 years of experience, I can tell you that the hardest thing for me to do was to stop writing code that didn't need to be written. Properties, functions, queries. If those don't work, then you can think about code. Once I understood how events worked which is also not that easy if you are not used to an event driven environment, I learned to use them to control my world.

One very important thing to understand about Access is that it is intent on not losing any data so it will save your data at points in time that confuse you. The only way to control it is to understand the Form's BeforeUpdate event. Think of it as the flapper at the end of a funnel. If the flapper is open, the data gets saved. If the flapper is closed, the data does not get saved and YOU are in control of the flapper if you simply put your code in the correct event.

 

[gemma-the-husky]

I think the biggest difference between Excel and Access is this:

Anyone can use Excel, and you don't need any code to produce a useable spreadsheet.
On the other hand, It's really hard to use Access, and you need lots of code. You have to carefully consider the nature and structure of your data, which you just don't need to do in Excel.

When you go on to consider Access in a bit more detail.

• You rarely, if ever see all of your data in an Access database. As a result, you can get data leakage and inaccurate outputs in Access very easily without realising it.
• With Access, a lot of the design construction is there to prevent users (including the developer) from using the full power of the system. It's that dangerous, and there's no failsafe "close without saving". There just can't be as it's concurrent multi-user.
• Although an access datasheet looks like a spreadsheet, it's not the same. The reason is that the access datasheet is a collection of rows of data, rather than a whole sheet of data. Each row is independent, and the "order" you see is illusory. There is no next or previous row, so loads of things that you can do in excel are just not available in Access. Instead you have to think about managing entire sets of data. It's a different, and much harder concept to understand.

 

[CJ_London]

• You rarely, if ever see all of your data in an Access database. As a result, you can get data leakage and inaccurate outputs in Access very easily without realising it.

equivalent for excel

• you rarely can easily see how data is calculated, as a result calculations may be changed in individual rows and cannot be easily identified resulting in inaccurate outputs from Excel without realising it

 

[oleronesoftwares]

Welcome @4mal to the best online forum for Ms Access Developers

 

[GPGeorge]

Welcome aboard but you are making the typical uninformed non-Access user mistake. You don't actually understand what Access is.

Access is NOT a database. PERIOD. MySQL and SQL Server can't do anything that Access can do is the proper way to look at it since they can't develop applications but Access can. They are database engines, not application development tools. Access is an application development tool. In fact it is a RAD tool. It is NOT a database. Jet and ACE are databases. They are designed to work on a desktop or a LAN. When Access is connected to a Jet or ACE BE, Access is constrained by the limits of that BE. i.e. a physical max of 255 users but a practical max of ~ 50 concurrent users. However, when Access is connected to a SQL Server BE, it is constrained instead by the number of seat licenses you have for the server so if you have thousands of seat licenses, you can have thousands of concurrent Access users. So, Access is as expandable as you want. The weakness of Access is that it does not easily support multiple simultaneous developers so perforce, applications built with Access rarely get very large (I'm not talking about the size of the tables). However, over the course of years, you can build anything so there are actually lots of huge applications written in Access. They were just created one feature at a time by a single developer or a series of developers over time.

The Access/Jet/ACE synergy is confusing and way too many people confuse Access with a database engine. They do not understand Access' connection and dependence on Jet/ACE. Access objects are stored in a Jet (.mdb) or ACE (.accdb) database container so Access cannot survive without Jet or ACE. However Access is not dependent on Jet or ACE for its data storage needs. Access can work with ANY RDBMS that supports ODBC. It even works with custom ODBC drivers such as the one for QuickBooks to produce add on functionality for QuickBooks. Jet/ACE have a stand alone exe and can be and are frequently distributed separately. They too can be used by any application that is ODBC compliant. Access however provides a GUI that works like SSMS for SQL Server. You use Access to create/manage Jet/ACE objects using a GUI if you don't want to use DDL or DAO/ADO code.

Access is not a general purpose tool. It is intended to be used to build data-centric applications that work on a LAN. You would never use it to build a graphic intensive game or a channel program (low level code that connects the operating system to various devices) If you need something else, you should find a different tool. Since Access is a RAD tool, it does lots of stuff for you which is very helpful to people who have no development experience. For people who do have development experience, Access is a real challenge because they keep trying to make it work the way they think it should work instead of adjusting their attitude to go with the flow. Let Access be Access and it will be easy and extremely fast as a development tool. Or you can fight with it and spend your life frustrated. Since I came to Access with 30 years of experience, I can tell you that the hardest thing for me to do was to stop writing code that didn't need to be written. Properties, functions, queries. If those don't work, then you can think about code. Once I understood how events worked which is also not that easy if you are not used to an event driven environment, I learned to use them to control my world.

One very important thing to understand about Access is that it is intent on not losing any data so it will save your data at points in time that confuse you. The only way to control it is to understand the Form's BeforeUpdate event. Think of it as the flapper at the end of a funnel. If the flapper is open, the data gets saved. If the flapper is closed, the data does not get saved and YOU are in control of the flapper if you simply put your code in the correct event.

I can't add anything much to that, except perhaps to expand on one of the more important reasons Access is unique.

All relational database applications consist of three layers, or tiers:

Data tier: Your data, stored in tables. More on this in a moment.
Interface tier: The objects through which users can interact with data; this is comprised of forms and reports.
Logic tier: The automation code which manipulates both the data and the interace.

Access is unique among development tools for relational database applications in that all three components are included in the single development tool. All three can, and frequently are, stored in the same file, i.e. the accdb.

However, Access is also capable of linking to any one of a large number of other database engines, even to non-database storage such as Excel worksheets and text files and SharePoint lists (we can argue whether SP is, in any feasible way, a "database", but that's beside the point. SP lists are not, themselves a "database".)

So, when you think about alternatives to Access, you must account for each and every one of those components.

 

[Pat Hartman]

• You rarely, if ever see all of your data in an Access database. As a result, you can get data leakage and inaccurate outputs in Access very easily without realising it.

Dave, can you give us a concrete example.

 

[oleronesoftwares]

When you go on to consider Access in a bit more detail.

• You rarely, if ever see all of your data in an Access database. As a result, you can get data leakage and inaccurate outputs in Access very easily without realising it.
• With Access, a lot of the design construction is there to prevent users (including the developer) from using the full power of the system. It's that dangerous, and there's no failsafe "close without saving". There just can't be as it's concurrent multi-user.

Never encountered point 1 and 2

 

[oleronesoftwares]

you rarely can easily see how data is calculated, as a result calculations may be changed in individual rows and cannot be easily identified resulting in inaccurate outputs from Excel without realising it

Do you mean, you most likely can see how data is calculated in excel?

 

[MajP]

Yes it is the magic unknown bullet.

 

[CJ_London]

Do you mean, you most likely can see how data is calculated in excel?

No - you see the result of the formula - 50/500/5000... rows repeated. But so easy for someone to change a hardcoded value in the formula or even replace the whole formula in a row here or there. And no one will know unless they click the show formulas option - and then they have to scan the whole column(s) to check the formula is the same.

 

[oleronesoftwares]

No - you see the result of the formula - 50/500/5000... rows repeated. But so easy for someone to change a hardcoded value in the formula or even replace the whole formula in a row here or there. And no one will know unless they click the show formulas option - and then they have to scan the whole column(s) to check the formula is the same.

OK

 

[gemma-the-husky]

Never encountered point 1 and 2

Sorry, but we surely all have.

Point 1. You rarely view an entire table of data in Access. Even less likely do you select a table, and view all the related date for all the child tables. There might be tens of thousands of rows in an "invoices" table. You almost never view that table in its entirety. Therefore, if because of sloppy design, some invoices don't have a related "customer", (or some other related entity) so when you view your sales by customer, your view will not include the sales for the orphan invoices, and you have no way of knowing this, unless the developer provides something appropriate.

Point 2. At a basic level, You don't want users deleting everything from a table, so you hide the tables, you build in RI, you disable cascade deletes, you add msgboxes to double check the user's intent, you limit a user's ability to create their own queries, and so on. - all to protect them from their own catastrophic mistakes. Even the developer can easily do a "delete * from" without specifying a particular record.

You don't do anything like this in Excel, because the user can just close excel without saving, or can save a copy.

 

[Pat Hartman]

I hope you are not saying that Excel is "safe" because you can close without saving. You have to be conscious that you made a mistake. Regardless of your environment, if you recognize that you made a mistake BEFORE you saved the data you can recover. If you recognize after the data has been altered, too bad for you.

 

[gemma-the-husky]

No. I'm saying Excel is much easier to use, but it's just a toy in comparison to a database. I don't understand how serious organisations, governments included, can get to a state of using Excel for mission critical applications. IT departments won't give users a break with Access but allow untrammelled use of Excel within organisations. I imagine they would get nowhere if they tried to stop the use of Excel, so they don't bother.

On the other hand, Access is not a toy, and requires levels of expertise and discipline far beyond the skillset of an average Excel user (which is probably why Access databases often don't work, and get a bad press, and which is also why awf and utteraccess are so busy).

 

[NauticalGent]

I don't understand how serious organisations, governments included, can get to a state of using Excel for mission critical applications.

Total madness. I often get asked by Excel users to "build a better mousetrap" - and everything is fine until I suggest something other than Excel. If I mention SharePoint or Access, they scatter like cockroaches.

 

[The_Doc_Man]

It all stems from discussions from IT guys back when Access existed but Gigabit networks did not. Since Access, even properly split, has to drag data over the network to work where the front-end lives, it is a network hog on a 10 Mbit network - and I remember those networks. The IT guys went stark-raving bonkers over network loads in Access shops. Excel did that more rarely because Excel didn't share as well, so they "liked" Excel. It didn't clog "their" network. But they didn't understand that Excel is to databases like an abacus is to calculus. When it comes to "real" database apps like inventory, personnel management, industrial monitoring, patient histories, etc... Excel quickly becomes a plodding pig.

The REAL issue that makes Excel so awful is this: EVERY CELL in an Excel worksheet is initially an independent entity. You need to program cell references into Excel to introduce dependencies. When you do a SUM in Excel, you have to list the start and end of the range to be summed. When you have a formula to compute something dependent on other cells, you need cell references. BUT with Access you can define things via a Query or perform VBA computations, leaving each "cell" (field) in a table as an independent entity. With Access, you can define a simple formula in the query. You can implement formulas in VBA. Which lets your data remain "pure." You leave the complex stuff to OTHER parts of Access that you don't always have to check.

 

[GPGeorge]

... You leave the complex stuff to OTHER parts of Access that you don't always have to check.

In other words, Access creates relational database applications, not databases.

A relational database application consists of three tiers:

Data tier: data in tables, pure and simple
Interface tier: forms and reports through which users interact with that data
Logic tier: VBA and macros that manage both the data and the interface.

By mushing interface elements and data elements into a single surface, Excel manages to lose the very qualities that make Access a superior tool for creating relational database applications.

And on a related note, keeping the data in the tables and the interface in the interface is a very strong argument against things like MVFs and lookup fields that drag bits of the interface into the table in a misguided attempt to "simplify" for the novice.