Is Microsoft Access a 'proper' database?

[gemma-the-husky]

I really don't think we manipulate access to prevent legitimate users doing stuff, and generally prying (although we may choose to do so to protect our IP

I rarely see that with excel spreadsheets. Businesses generally make spreadsheets freely available within organisations. Probably because of that, complicated spreadsheets tend to decay a little over time. total cells fall out of reference, lookups stock working correctly, and so on. But in truth, spreadsheet users are generally competent enough to use the spreadsheets, and the spreadsheets themselves are not over complex. They may seem so, but in data handling terms, they are not.

the problem with access is that it is an order of magnitude more powerful than a spreadsheet. an "ordinary" user cannot hope to use it correctly. Many so-called advanced users do not use it correctly either. So MS do not ship Access with Office as standard.

We HAVE TO complicate Access databases, not to protect the application, I think, but to protect against accidental destruction of important data by insufficiently skilled users. We add code to databases to LIMIT what users can do, (including ourselves) precisely because it is so very powerful.

All this is why I think an Access database is as legitimate as any other solution a business may use - but the solution itself requires careful development to ensure it meets the required specification.

Most commercial systems that use SQL Sever still provide ODBC drivers that enable a skilled user to interface with the data outwith the prescribed platform. We do not abuse that facility, more because we are aware of the inherent dangers of manipulating data directly in tables, and avoiding whatever control systems the developers provided - which is no different to what we do with Access.

To answer Jon's original question, I think Access is a proper and wonderful database manager/development environment

 

[GinaWhipp]

@ButtonMoon,

I was not intending it to be an argument against proper security, simply a reply to a comment I read. I'm leaving that last line alone...

 

[RainLover]

Unfortunately that kind of argument doesn't get you far when it comes to an organization's legal (and moral) obligations to protect its systems and data. Prosecutor: "Did you follow industry best practices and take all reasonable steps to protect customers, employees and shareholders against information security risks?", Defendant: (shrug) "The crooks are going to steal our data anyway, so why bother?", Prosecutor: "I rest my case".

Many companies do of course prohibit the use of both Access and Excel on production servers.

I agree that protection of the data is paramount. So in future

No Excel
No Word
No Outlook
No Text files

No note pads and pens. Those commies will use anything to steal our secret data.

Best get rid of our clients because they may sue us.

Either that or get real.

 

[nfk]

IMHO Access has always been perfect... for single mothers and retired chaps in need for a hobbie like creating a receipt database.
Its extremely frustrating to work with, there is no real help in the communities and its the most elitist driven support I know.
By accident im forced to work with a dozen Access frontends created by yet another MVP dinosaur. Its aweful for multi user usage and it crashes from computer to computer, from wOS to wOS, from service pack to service pack, etc... Its a mess, its far too complicated to do things that with any proper RAD you would do in a few secs.

now...

I been taking a closer look at the new Access 2013 working with Sharepoint server for small business and I got to say so far it does gets my attention, I think its one of the most sexiest things I've seen coming from microsoft in a while... I like the whole concept of building a DB, a frontend and delivering though the web... My faith in Access might just been restored...

 

[CJ_London]

@nfk - glad you're honest

Its a matter of horses for courses and Access has it's place. If you're going to ban Access because of data security issues etc, better ban Excel as well.

IT departments have tried for years to ban Access but have failed because they are unable to produce the required solution in the (market driven) required timescale and budget.

No help from the communities? - show me one that is better than this one

Elitist? - you're entitled to your opinion but those using 'proper' db's can be just as elitist

Multi user? - if you set it up right, it is not a problem. Certainly as far as front ends go - build an Access front end to a 'proper' db and you can have as many users as that db will allow.

Crashes from computer to computer? - usually because it has been poorly built - and that happens with any system.

Crashes from WOS to WOS? - have you tried using Explorer v1 (or even v8) recently? Things move on and the access application needs to move with it. As does upgrading from SQL Server 2000 to 2008, etc

Far to complicated? All depends on your level and area of expertise - sounds like yours is in a different area

Fit for purpose? - so many 'proper' dbs out there are either incapable of change (for a sensible amount of money) or the client has to have deep pockets. This is often because of poor design specifications or cutbacks on original specification due to budget constraints - which can happen to any system.

Performance? - admittedly Access can be slow across a network, but put it on a terminal server or Citrix and it's as good as running on your C drive, but then it's running on a server so you are effectively getting server side operations, just like a 'proper' db.

I've lost count of the number of times I've supplied a client with an Access solution because the 'proper' db is out of date with real world requirements and will cost too much/take to long to update.

 

[nfk]

I agree, i agree... on some points...

But on the OS point I do not agree at all, I do understand your point and I think its totally fair if we are talking about personal usage or small business, tops. What if we're talking about a big organization or a government? would you expect them to change everything every 5 years because someone in the past decided that Access was fair enough? That's not right, I have code solutions back in Windows XP time that still work with 8.1 and I bet both of my legs they will work with 10, also users don't need to pay a licence every once and a while it all runs smoothly... I go into Access just to find caos...

BUT! as I said earlier I think I might just got a new pal in the microsoft side... I got an Access 2013 licence yesterday and im currently donloading Sharepoint server to run tests...


----

On a side note, have anyone noticed that on the search for Access resources online 9 out of 10 times if you can see an avatar of whoever is ansering that person will be in his mid 50's, also websites displaying Access tutorials and such all look like straight out Geocities? I dare anyone to proof me that Access <= 2010 is not an ancient tool for elistist advertising troops of MVP's.

 

[GinaWhipp]

HELLOOO!!! Can we move this conversation to the Water Cooler? As we are starting to digress...

 

[CJ_London]

HELLOOO!!! Can we move this conversation to the Water Cooler? As we are starting to digress...

Think I'm done, just had a bit of spare time on a Friday afternoon

 

[nfk]

Think I'm done, just had a bit of spare time on a Friday afternoon

That's exactly what i'm talking about when I say 'elitism', thanks for making my day.

 

[gemma-the-husky]

IMHO Access has always been perfect... for single mothers and retired chaps in need for a hobbie like creating a receipt database.
Its extremely frustrating to work with, there is no real help in the communities and its the most elitist driven support I know.
By accident im forced to work with a dozen Access frontends created by yet another MVP dinosaur. Its aweful for multi user usage and it crashes from computer to computer, from wOS to wOS, from service pack to service pack, etc... Its a mess, its far too complicated to do things that with any proper RAD you would do in a few secs.

now...

I been taking a closer look at the new Access 2013 working with Sharepoint server for small business and I got to say so far it does gets my attention, I think its one of the most sexiest things I've seen coming from microsoft in a while... I like the whole concept of building a DB, a frontend and delivering though the web... My faith in Access might just been restored...

this is plain wrong.

if people design a badly normalised data structure, either through lack of competence at normalising data, through not understanding the customer's requirements, and then cannot implement the solution, as they do not have the required coding skills, and as a result they are unable to make it work as it should, the problem is the designer, not the database.

if you create a correct and well normalised data structure, then access (or any other) database development becomes harmonious and straightforward.

When you say there is no support available, I can only say that I am amazed at the code I have found available on the internet, provided freely by generous developers. Based on some code I found, I have for example developed a system that enables me to maintain database code by iterating all my code modules, and modify that code to add or maintain certain code fragments.

I doubt very much if access MVP's are designing badly functioning apps.

 

[ButtonMoon]

if people design a badly normalised data structure, either through lack of competence at normalising data, through not understanding the customer's requirements, and then cannot implement the solution, as they do not have the required coding skills, and as a result they are unable to make it work as it should, the problem is the designer, not the database[sic]

The irony is that one of the arguments most often advanced by people who champion Access over other products is this: that it allows users who are not necessarily data management professionals to bypass and ignore the qualified, experienced professionals in their IT Department. Some in the Access community have thus promoted and continue to promote Access as an enabler of incompetence and "shadow IT" in the work place. You don't have to look far in this forum and elsewhere to find examples of that kind of advocacy. I can sympathise with the point of view that there isn't necessarily a lot of quality support around for Access (compare with the .NET, Java or general data management community for example).

The next time someone tries to promote Access as "easier" or even "cheaper" to use than the alternatives I'd be glad to quote you in support of my point of view, which is that it depends on the competence of the person doing the job and has nothing to do with Access as a product.

 

[Uncle Gizmo]

The marvellous thing about MS Access is that everyone has it!

The terrifying thing about MS Access is that everyone has it!

This is its advantage and also its curse.

Access allows novices to build systems that might not follow all of the rules. In fact they might be in a flat file format like an Excel spreadsheet. However this isn't a problem because this is a tried and tested way of building a database. (I understand some of the most successful business products are based on a flat file system). Just because you don't use all of the features of MS Access namely its relational nature, doesn't mean there's something wrong with your database, it just means it could be done in a better more efficient way.

The beauty of MS Access is that it allows the amateurs to very easily convey what they need and want. It's a marvelous tool for developing a specification directly from the end user. So although you might think some of these databases these people create are disasters they are in fact very useful. Most of them just get used for one simple task but some are expanded well past their initial task. And it is these databases that cause the IT guys of the business a headache! But for an MS Access developer they are a gift, an opportunity, even though they might be a headache at first!

 

[nfk]

I see fair points of views here but there's also huge amounts of denial over things that are so dramatically obvious. Maybe I was being to much of a dork and since i'm swimming in vast amounts of frustration on a daily basis I loose a ton of coherence on what I'm trying to say.

Access its an old system that its seeing its last 10 years of service the way old MVP's know it, the communities are old and in many cases no longer sustained, that's alright, a lot of people might say "f*ck it! I'm not getting paid to answer spoiled kids" I understand that, but I'm also talking about the official support, the one that Microsoft seems to delegate on his yearly Oscar awards to a handful of users over the net, but who really, honestly is there to answer (No post count+thanks+rep)?.

I'm no master in programming but I have code many applications of a variety of ranges and never in my life came to something as messy as VBA, as hard to handle, has frustrating to even look at... The amount of code it takes to make a single bloody report... A single button... ughh...

Its not the worst thing in the planet, I mean, there's still Cardbox and Dataease... but there's so many awesome, clean, standalone-making RAD's out there now that I really don't understand why someone will take his chances with Access 2003, 07, 10...

I was taking to a guy the other day and he was all over the place with Access so much into it he would never touch mysql and how shocked was this dude when taking a look at the marvelous FREE mysql workbench provided by Oracle, how easy is to work with, then take your pick on what do you want to use to build the front-end, if you need more power then move so SQL Server, no probs... Visual Studio, DOT.NET, XOJO, windev... At that point its about budget...

I'm not 100% against old school Access I'm just pretty sure its time to move on and I can see Microsoft is also doing his job at killing ADP and the old generation of Access, new rules in the set and new technologies, now the issue is to migrate the vast amounts of wasted dbs to something the user can actually enjoy.

 

[Uncle Gizmo]

Access its an old system?
So what about Excel, Word, Outlook?

Access its an old system?
So what about Alpha 5 the only real competitor to Access?

Access its an old system?
So where is the something better I can use? VB.Net? No thanks....

MS brought out VB.Net a while back, a free version. I gave it a try, you had to drag in connection objects, write code to link up to your text boxes, and all sorts of hoops to jump through to achieve the things that were so simple in MS Access. VB.Net didn't have anything like a sub-form control which is so useful, the bedrock of MS Access. I don't think they even have an equivalent now do they?

So where is the something better I can use?

I have for along time thought MS will phase out MS Access, the writing was on the wall up until recently, now I think MS realise Access is useful to them, but who knows!

 

[RainLover]

I found this an interesting read.

How some people judge a Database by its security alone? Is Access a Database, or is MySQL SQL Server Oracle etc the real Databases.

Access merely is a development tool that allows you to enter, store, and retrieve data in a simple no fuss manner.

Security, Multi User etc are optional extras.

My First Data Base in Access 97 had by intention no Security, it was not split and designed for one person to use. It was a membership tool for our Local Scout Group. Only the Group leader used it and was able to produce reports for the leaders of each Group and the status of their section.

I really did not know much about this development field and it was a constant task with improvements as I learnt them or extra requirements become obvious.

This is one of my first Access real life stories. It is true. I am sure that there are Millions of stories just like this one. Databases written by Administration staff because of their advanced knowledge of Data Base. Namely, they could reboot the server or something similar.

I have not written a large Data Base in quite some time. I mean something that I have charged $1,000,000 Figures for it. In addition, to them that was cheep because it did exactly what they wanted. If it did not then I fixed it, at an extra charge.

To turn the plate around. If the boss wanted an attendance book, would you go the excessive cost of a SQL Server back end or do you think Access in any version will do the job so well.

Access has its place. Those of you who cannot understand this may be better off with excel or perhaps the good old index cards.

As a child I was taught, a place for everything and everything has its place.

Should we not all open our Minds as to the Access's simple workings through to its very advanced abilities for an advanced designer?

As an example of something good in Access have a game of chess. I just noticed a little fault in it. If you happen to come across it and have a solution please let me know. If not I will look at it myself in a day of two.

Thanks for the discussion.

 

[The_Doc_Man]

Having looked at many "modern" systems to do databases with various and sundry features included embedded code somewhere behind the scenes and all sorts of relationships, I have to say that Access is not worse than any of the others, and is perhaps a bit better than some.

I got involved in a mainframe system associated with the ShareBase database machines, which were UNIX-like dedicated SQL systems. They had design-by-template methods for forms and reports, and used BASIC for event-like code. I can't tell you how many abominations were perpetrated on the developers. However, don't look for ShareBase, they got bought out by TeraData and ceased to exist some years ago.

There was a trend for a "PeopleSoft" database for HR/Personnel situations some time ago. It was a crock because it depended on people agreeing on the rules first - but PS was a data-driven system such that once you said what something represented, the tail wagged the dog! I.e. PS made the rules and you had to hope that yours matched up. Or you could have paid PS a ton of cash to customize your non-standard personnel data. In our case, we decided that only 19% of PS existing code matched what we needed. Or you could try your hand at writing your own PeopleCode - which the sales people said wasn't programming - it was just data. (Yeah, and pigs fly into the stratosphere...)

Want to use ORACLE? Just watch out for the embedded PL/SQL code. You can use ORACLE Tools to do the design of the forms and reports. (But at the same time we looked at PS, we decided ORACLE matched 18% of our needs.) I should point out that the U.S. Government made its decisions on such tenuous statistics. However, since then, they've become at least a bit more skeptical. But only a bit...

I used to work with a pipeline control systems company that tried to predefine an algorithm for leak detection. After a few years we had 24 customers and 25 leak detection algorithms. One which was ours, the other 24 were theirs.

I remember when Paradox for DOS converted to Paradox for Windows. There, you had query by design, query by example, and Pascal-like event code. The problem with Paradox for Windows was that it didn't show you all of the possible events, whereas Access showed you events you had never even heard of. Two extremes - but at least with Access you could ignore the events you didn't care about. With Paradox, if you cared about an event but Paradox didn't, tough luck Charlie.

The point I'm making is that Access VBA is not particularly worse than any of the other "behind-the-scenes" code systems. You have a large number of "hooks" to get you into things that you might wish to see. The Access design GUI is not terribly worse than other systems, just clunky at times. The wizards are dumber than a box of rocks - but they let you put together a customizable skeleton very quickly. The object orientation of the features behind the scenes? Not great - but you can get there from here most of the time.

It is not going to EVER boil down to "which one is better" - only to "which one you like" or sometimes even "which one do you already have" or "which one can you afford to buy?"

Let's not forget that at least some of the complexity of Access derives from the fact that it is running under Windows, which may be one of the clunkiest O/S's I've ever seen, and I've seen quite a few.

 

[Uncle Gizmo]

>>>Now Uncle don't teach your grandpa to suck eggs<<<

I would never be as so bold as to presume......

I am aware you are an experienced blogger,,, I suspect some of the; shall we say newer bloggers think that what they lack in experience can be made up for by being crass and irritating. (that's a couple of words for the inexperienced blogger to Google) Little do they realise that being older doesn't mean we have forgotten how to "Not Behave" it's just we choose not to miss-behave. Now if there was a "Miss" here... Oh I nearly forgot, not allowed to go there...

 

[RainLover]

Let me ask this simple question. If Access is not a true Data Base what then was the first. Who came up with the name and then to finish the DOB of all their children.

I got my information direct from the horses mouth. We has a seminar here in Australia which was attended by various dignitaries and guest speakers. The subject was the future of Access, in particular 2010.

Unfortunately the only witness I have is no longer with us.

This thread is poor in quality as it lacks almost everything.

Perhaps you should define what a true data base really is.

To me it is a place where information can be stored and later retrieved. Does this make your newspaper, or MS Outlook a databases.

Have fun .

 

[Brianwarnock]

Good to have you back Rain.

You are correct to ask the doubters for a definition, no doubt the hierarchical DBS like IMS would cause them to choke on their sushi

Brian

 

[ButtonMoon]

Rain,

I'm happy to use your own definitions to make this easier. Access is a "development tool" (your words) that can be used for creating applications and databases. Access is therefore no more a database than Microsoft Word is a document or Microsoft Excel is a spreadsheet. Roughly comparable with Access are other development tools like MS Visual Studio, SQL Server Management Studio and Oracle SQL Developer. Those are pretty obviously not databases either.

To me it is a place where information can be stored and later retrieved

That's a reasonable definition of a database, I agree. A database is analogous to a warehouse. If I store and retrieve things in a warehouse that doesn't make me a warehouse does it? Equally, the fact that Access is an application tool that can be used to store data in and retrieve data from a database does not make Access itself a database.

Let me know if pictures or a song and dance routine will help explain the concept of databases any better because this thread is pretty dull!