What's wrong with Access?

[aaronb50]

A little over a year ago I had 10 classes left for my Computer Science and Information degree. I had taken a few different introductory courses to a few different languages but it seemed as though I wasn’t really learning enough of anything to really do anything.
I lucked out and got a good job. But they found out I knew how to write a little code and handed me a simple Access program one of my co-workers in another department had written them. At the time I didn’t know what Access was, but as it turns out it’s just a less complicated Version of VB which I had taken 3 classes in and was familiar enough with to take that program they gave me and turn it into something amazing.
My boss saw the potential and we have not looked back. I had created myself a programing/DB management job. Little over a year later I’m on my tenth program, all made with a front and back ends in Access.
Just last week I mentioned using Access in a research paper I did for a relational database class. Today my teacher in his comments mentioned he would not recommend Access unless cost was an issue because it does not scale well.
Over the last year I have been getting the feeling that in general, the programming and Database world kind of look down at Access.
Why is this?
This is my first real experience doing what I’m doing and I’m kind of making it up as I go along. I’m starting to feel like I should be looking for something else other than Access.
I have not come across anything Access can’t handle, and I’m sure it does way more than I have needed it to do so far.
But this attitude I seem to be picking up on is making me question what is wrong with Access, and do I need to start considering moving on to something else?
Any thoughts?

 

[Frothingslosh]

First off, while Access does certainly have its problems, a lot of IT types still look down on it simply because so many people try to set it up like a spreadsheet and run into no end of problems.

Now: Access, like any other DB program, is a tool, and it does have its limitations. It doesn't work too well with Sharepoint and general internet use, and there is still a 2 GB file size limitation. It's also known to be a network resource hog, as the program can be VERY chatty. (In the interest of fairness, much of the chatter can be eliminated by intelligent use of indexes and care in choosing which functions to use and how to use them.) It also starts running into issues when more than a handful of people use the same backend at the same time.

That said, it's an excellent tool for small businesses and reasonably-sized projects. You won't see GM using it as a parts database, but it's routinely used in the business world for things such as inventory control, accounting software, business management, even pawn shop ticket systems. It works well for businesses that have no need for massively powerful, rather pricey custom solutions from Oracle and the like. You'll even find it used in major corporations for internal projects - right now, Blue Cross/Blue Shield is actually looking for an Access developer for an internal project, and I was approached a month ago about a university that's looking to replace their entire student scheduling system with a custom-built Access application.

Now, with all that said (yeah, I like the phrase), learning to use other tools can only help you. SQL Server would be a great start, as it's actually faster and a bit more powerful than Access as a back end, and plays much nicer with networks. It's even not particularly difficult to convert an Access back end into a SQL Server back end, and hooking an Access front end into SQL Server is easy. Oracle is another one to look into, but bear in mind that Oracle software is pricy, or at least was the last time I looked at it.

 

[Christos99]

Hi,

In my opinion there is little wrong with Access (surprise being on this forum). I have been developing in Access for about 20 years, usually these days with a SQL Server backend which solves a lot of scaleablity / security issues.

I still find people look down their noses at Access in large companies mostly because they don't know better. Another issue is the number of BAD Access databases that people have written without proper programming knowledge because it is on most peoples PCs via Office.

The only thing is if you are thinking of getting a job elsewhere you may be quite limited with only Access knowledge.

Cheers,
Chris

 

[Frothingslosh]

The only thing is if you are thinking of getting a job elsewhere you may be quite limited with only Access knowledge.

Amen to that.

 

[aaronb50]

Thanks, I feel much better!!! I was starting to think it was all going to blow up in my face for some unknown reason. But I think these programs are going to be ok. I absolutely love Access!!! But yes, I do realize at some point I’m going to have to broaden my horizons.
When I was originally learning about what Access was, I did read about how it was a pretty simple transition to take an Access back end and convert it to SQL. I don’t know how to do it but I have always thought of that as my escape route if these backbends ever got out of hand.
One program looks like it’s on the cusp of become an enterprise solution. I think if/when that happens I will take that back end and turn it into a SQL. I know our company uses it and I should be able to get access to it.
What’s a good place to start learning how to make that conversion?
I have played with SQL in a few classes I have taken so I’m a tad familiar with it.

 

[pbaldy]

Access is a tool. Like any tool, it's perfect for some jobs and ill suited to others. Leigh has a lot of info about using SQL Server here:

http://www.utteraccess.com/forum/Beginning-Sql-Server-Deve-t1732935.html

 

[namliam]

access IMNSHO is definately a undervalued tool and VERY looked down upon by most of the IT world.

Meanwhile it has definately got its pro's (and con's)... biggest pro is its flexibility and development speed. I fixed a problem with access inside of 2 days work that was ESTIMATED at 3 weeks development by "proper" IT people. Plus those people were not available for the next month or so.

** Shameless self promotion comming up **

Also have several access programs running already for 10-15 years and MORE without much issues even some IT people trying to kill them without much success becuase their development is just way too long. Biggest chance the had for some of these (as they call it) Access shit datacraps was the recent implementation of SEPA.
SEPA took them over 6 months to implement into the systems and they still screwed things over.
I promissed a fix inside a week and implement a proper SEPA fix in less than that week based on the old banking principles in the proper IT systems.

Yes access will have (IT) people look down on you and after a while will make you frustrated (as am I at the moment and hopeing to move to a "proper" IT place soon-ish).
However the business that require a fix today and not a month from now, will love you for it

 

[aaronb50]

Ok yeah I’m going to just keep going with Access and figure out how to use SQL for a backend when needed.
“However the business that require a fix today and not a month from now, will love you for it ”
Yeah I built them a program in two weeks with Access. They see it working and plan to use it first sector wide and then the whole company. I was told yesterday that when they looked into having something built from a third party they were quoted one million dollars.
We had 20 year old FoxPro database(I actually posted something on this form about it asking about the weird characters) that I took and turned into a shiny new Access database. Granted it did take me a month to convert all the unprintable characters into something I could use and then figure out how it all went together, but it was better than the 750k they were quoted to do the same thing by a third party. It’s amazing what you can do with Access in such a short amount of time. It almost seemed too easy and I was getting a little worried. But I’m a true believer now and I’m going to ignore anyone who just doesn’t get it!!!

 

[RainLover]

There has been some very good comments here. They have been honest and fair as far as I could see.

My only disagreement is with the use of SQL.

If your company has it up and running and the proper people in place to keep it running then fine. Otherwise it is like a burnt boomerang coming back to poke you in the eye.

MySQL is used far more extensively and it is free. At an educated guess I would say that 90% of all web sites use MySQL rather than SQL Server.

Such are my thoughts.

Time to eat.
The Koala looks like it is done.

 

[ButtonMoon]

Just last week I mentioned using Access in a research paper I did for a relational database class. Today my teacher in his comments mentioned he would not recommend Access unless cost was an issue because it does not scale well.

Your teacher may have more than one thing in mind. He may just mean Jet/ACE - the file-sharing data engine that is bundled with Access. Jet/ACE is designed for desktop/file-server database applications and it is not a client-server DBMS. It doesn't support anything like the kind of scalable, secure, highly-available, fault-tolerant and server-based solutions that are possible with the industry standard SQL DBMSs from Oracle, Microsoft, IBM, etc. Client-server DBMSs are so cheap to buy and own these days though that there's no great need or demand to use Jet/ACE for those kinds of applications anyway, so your teacher would have a point there. Aside from its lack of scalability, Jet/ACE lacks many of the important modern database features that you might be expected to learn about in a database class.

Then there is Access itself. Access is an application development tool that supports all kinds of database (and non-database) solutions. Because it is an application development tool it probably only has limited relevance to your relational database class. I've found that the Access UI often can be something of a hindrance to students of databases because it presents things in such an unconventional, inconsistent and sometimes positively obstructive way when compared to other database management and development tools. Access can be used with a range of relational or non-relational databases but the features of its user interface and the highly proprietary, non-standard way some concepts are treated make it a great tool for developers but generally a poor one for students or teachers of database management.

 

[spikepl]

Today my teacher in his comments mentioned he would not recommend Access unless cost was an issue because it does not scale well.

Your teacher has his academic head up his academic ass.!!!!!!!

Cost or scaling are not necessarily the determinant factors for choosing some tool for some job and a teacher ought to know that!

Access is a combo of a DBMS and a application development tool, and most detractors forget that a DB is worthless without a user interface. Further, the way Access is, it is part if the Office and therefore many times can bypass various IT-imposed strictures. Also, anyone - as evidenced by so many professional amateurs on this site - can get cracking using Access, which often irks the so-called "professionals", because utmost crap gets developed (at a fraction of the cost that the pros charge for crap) . But it WORKS .. and before IT manage to respond to some request with a counterrequest for a cost code ...

So just get better at Access ,.. but of course learning a DBMS like mySQL or MS SQL can show you many additional features that are not available in Access, but yet can be used thanks to the pass-through option in Access.

 

[bob fitz]

There has been some very good comments here. They have been honest and fair as far as I could see.

My only disagreement is with the use of SQL.

If your company has it up and running and the proper people in place to keep it running then fine. Otherwise it is like a burnt boomerang coming back to poke you in the eye.

MySQL is used far more extensively and it is free. At an educated guess I would say that 90% of all web sites use MySQL rather than SQL Server.

Such are my thoughts.

Time to eat.
The Koala looks like it is done.

Hi RainLover
Do you know if I would be right in thinking that only the most basic version of MySQL is free and that is also the case for SQL Server.

 

[ButtonMoon]

Hi RainLover
Do you know if I would be right in thinking that only the most basic version of MySQL is free and that is also the case for SQL Server.

There is no such thing as free software, but yes, you have to pay extra for features and support over and above the "community" GPL version of MySQL.

On Windows, Microsoft SQL Server is in my view a much better value deal than MySQL. You get more in the box and you benefit from better integration with the Microsoft stack. If you need the support for other OSs then MySQL is obviously a better alternative, although I would suggest that PostgreSQL or even the "free" versions of Oracle are technically superior DBMSs.

 

[gemma-the-husky]

Seriously

2Gb is an enormous of data for a well designed database. Millions of records. For many SME's Access is an excellent choice.

Yes, SQL and MYSQL offer some extra facilities, but Access is very very powerful.

 

[Brianwarnock]

As an exIT pro I object to all the anti IT sentiment on here, often the terms and conditions of the IT. Group are far more stringent than those outside. This was shown in a company I worked for when a non IT. Project went tits up an they were found to have no recovery procedures. However I do also agree with all the comments, pretty much for the same reasons. We had an amusing situation one year when the combined spending on IT From non IT Managers was greater than in the IT group as they purchased PCs etc to solve small local problems.

Brian

 

[Lightwave]

I would steer well clear of taking advice from people talking about technologies about which they know little irrespective of their title. Their views are likely to be echos of hearsay with strong confirmation bias ie there are serious signal and noise issues.

If someone makes a good solution using Visual C and someone else out of Visual Basic - what's the better solution....?
The right answer should be - can't tell either could be would have to see what its going to be used for and whether the users like it.

Any IT professional or user of IT needs to make fast accurate solutions for people , and as it stand big corporate departments aren't particularly good at this. You as a single individual able to control everything are much more likely to see the whole scheme of things and be flexible and implement quickly.

We recently had a case where an outdoor centre was looking for a booking system. IT were handed the project and they went out to tender for the project. They hired and started designing a system. 9 months later they had spent £10,000 on a basic system but no reports. IT came back to the centre manager saying that reports were going to cost an extra £10,000 and as a result the project was being dropped. Meanwhile the centre still needed a booking system and the manger was just pissed off. On a night out the manager came up to me and said you know about databases don't you, could you look to put something together.
(I m an accounant in the planning section)

I said sure I'll have a go - 3 months later I had a working system which at last count has taken 3,250 bookings and collected about 175k in receipts. The manager was totally chuffed and its significantly reduced her stress levels.

Going forward its a digital solution - could potentially run for years and with a bit of love and attention it literally will never deteriorate.

Imagine if your push bike never deteriorated and you could duplicate it infinite times - why design a new one if it is doing the task and literally costs nothing.

Need security / massive concurrency - just use SQL Server backend - its easy to learn compared to the intricacies of front end development.

In a two tier LAN environment for really complicated admin person input based processes hands down go with Access every time.
I am still looking for a Internet solution that allows me the flexibility of Access.

 

[RainLover]

Hi RainLover
Do you know if I would be right in thinking that only the most basic version of MySQL is free and that is also the case for SQL Server.

Bob,
There are versions of SQL Server that are free but have their limitations.

On the other hand there is MySQL where every version is free. The benefit I believe with MySQL is that if you wanted to create a Database e.g. a commercial venture, then the host would have MySQL installed and available for use at no additional cost to you.

There are many other advantages which could be argued. We need someone with more experience than what I have to argue in its favour. I have only used it once and found it very similar in difficulty to Access to use.

As a challenge I would suggest that you do a price comparison.

Hope this helps at least a little.

 

[gemma-the-husky]

fwiw, I recently installed both MySQL and SqlServer, for test purposes, and I must say I found the former far easier to install and use.

 

[RainLover]

fwiw, I recently installed both MySQL and SqlServer, for test purposes, and I must say I found the former far easier to install and use.

Transferring the Data would have been easier in MySQL too.

 

[aaronb50]

Any IT professional or user of IT needs to make fast accurate solutions for people , and as it stand big corporate departments aren't particularly good at this. You as a single individual able to control everything are much more likely to see the whole scheme of things and be flexible and implement quickly.

And I think this is exactly what’s going on.
I have found in general the more people I have to work with the less control I have and the longer it takes to get something done.
Never really looked at it that way but I guess if I apply that theory to creating databases, if one person can do it regardless of how, it should take a lot less effort then dividing that same job up between 10 different people.
Having complete control and doing it all myself, from an initial idea for solving the problem all the way to debugging the program, I have complete control to get something out to my company in a very short amount of time.
I can see how some projects can require a full team of experts, but for the programs I’m creating, one person with Access is way better than paying one million for a team of people to do the same thing.