Hacker News Reader

Comment by mattmanser | Hacker News Reader

Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers or get some proper programming training rather than a SAMS teach yourself VBA in 21 days book (OT, do they still have them?).

But most businesses won't realize that/be too cheap to spend the money to save much more money. Although it's much better these days than it was 5 years ago.

Though I know a lot of MI people where most of their job could probably be automated.

View on HN

Speaking as a Real Database(tm) bigot, Access has two key advantages over Real Databases.

1. It's there.

Most large organisations stump up for Office Pro, and Office Pro includes Access. The corporate policies prevent you from installing the Real Database of your choice -- you can only use what's already installed. Happily, that includes Access.

2. It's upgradeable to a Real Database.

Microsoft make transforming Access into a true multi-user SQL database fairly straightforward: install SQL Server and run the upgrade Wizard.

If SQL Server is not your personal favourite Real Database, then with a bit more work you can get Access to talk to something else via ODBC. Not as seamless, but still a clear upgrade pathway.

One of the projects that made me realise I wanted to be a developer and not a lawyer (long story) was an Access database I wrote for my part-time job. An errors-tracking system. I calculated that it saved the company 35 hours of manager time per month.

What did they have before that? A physical book, typed into an Excel spreadsheet once per month.

Access is a tool with unique bureaucracy-dodging properties. It's important not to discount those.

Seconded- Access was my gateway drug into development, too. Well, not quite- I knew I wanted to do it and had played around with PHP in my spare time, but after graduating college I found myself in a crappy administration job. They had a crappy Excel-based tracking system, I replaced it with Access. Then that grew into a SQL Server-backed VB.NET application. Then I left, went into another .NET job developing C#, then journeyed into the world of Node, Python and Ruby.

TL;DR: Access is a great place to get started.

> Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers

I hear this all the time, I wonder if you could expand on your reasons for this belief. Is Access always the wrong tool for any job?

I know of a few smallish (5 to 25 or so employees) that have been running their companies for well over a decade on quite large and complex custom written Access applications that were developed for far, far less than it would have cost to have it done "properly".

There are hard limits to Access's internal engine (JET, IIRC) which you're likely to run into as time goes on. The unfortunate thing is that there isn't anything you can do to avoid the limits or recover once you've hit them.

On the other hand, you can install Postgres under Windows and then build an Access frontend over ODBC. This gets you the best of both worlds, IMO, at the cost of running a kind of odd stack. Cheaper than SQL Server though, and I like the maintenance story better.

TBH the last time I considered a solution like this I was consulting, so it was a few years ago, and all my clients either didn't sign up at all or went for a custom web solution instead, so I don't have a lot of war stories about this platform. I do think it would work though.

JET's days are long gone. The SQL Server Desktop engine is the underpinning for Access now. It essentially _is_ SQL Server. The biggest limitation? The concurrent transaction count is limited to 5. There are others, but it is a far, far cry from that prone to corrupt, slow beast JET.

Actually, in the latest Office 2012, Access apps are now three tier apps with SQL Server 2012 or SQL Azure backends.

Point of clarification, concurrent transaction count is limited to 255. http://office.microsoft.com/en-us/access-help/access-2010-sp...

JET isn't all that bad. It can be used to run SQL queries CSV files. As an ETL tool it's pretty flexible.

It's amazing how much things change when you're not paying attention. Thanks for explaining this.

One of my clients has a poorly designed access system (using, now, mssql as the backend). It's not access's fault that the system is shit, it's the developer's fault.

Bad devs write bad code. Beginner bad devs tend to write their bad code in Access (or Excel). It gets the job done --- until it doesn't.

I agree with most of what you are saying, but I just don't see how this tool solves that problem. Both these options seem to be middle steps between the layman and full fledged software development. The Access/VBA route is slightly closer to the layman side while the DataNitro route is closer to the programming end (if you are the type of person who thinks Excel is a great front end for your database interactions, you probably aren't completely fluent with SQL). I don't see what advantages DataNitro provides that are worth jumping through the extra hoops.

Once you are going down the road of actually writing code you may as well just take the plunge and use a nicer language than VBA.

VBA isn't really going to be any easier unless you already have visual basic experience in some capacity.

If you use a "proper" database you're also going to get advantages in robustness and ability to easily deploy over a network. My last memories of using access for anything (admittedly about 10 years ago) was that it was prone to performance issues and data loss.

It also doesn't do some extremely basic things properly, e.g. foreign keys. Basically Excel -> Access is easy. Excel -> SQL is easy for a proper programmer. But Excel -> Access -> SQL is a complete nightmare.

In the end Excel -> Access is a short-term cheap, long-term very expensive decision. It means you invest a lot of money and effort in a tool that can't do databases properly and can't do forms properly and when you want to move to something that can do either properly you have to do both completely over again without any reusable code as no-one uses anything vaguely resembling VBA any more and Access as a DB that positively encourages the inexperienced to do a lot of things wrong.

I am sometimes perplexed why MS hasn't released VBA# yet. It's like there's some petty war that has been going on for the last 7 or 8 years between the office team and the .Net language team.

Bringing proper development tools to Excel is the single biggest thing Microsoft could do to undo decades of damage Excel has caused.

Access can be a convenient way to distribute structured data that is relatively static to other people, such as colleagues in another office, when it is not worth the effort to get approval for and implement a longer term solution.

I'm surprised that Microsoft has stopped including it in the more basic versions of Office, since it encourages people to seek better solutions who otherwise might stay completely in the Microsoft ecosystem and eventually move on to MS SQL Server.

> Access can be a convenient way to distribute structured data that is relatively static to other people, such as colleagues in another office, when it is not worth the effort to get approval for and implement a longer term solution.

Right, but that's an artifact of broken bureaucracy -- its not that Access is the right tool for the job, its that its the best tool left when bureaucratic controls are misapplied to prevent the use of the best tool for the job.

I'm not convinced that Access is automatically the wrong tool for every job. I worked with a team that had remote salespeople that were all over the place, with laptops and a custom VB custom configuration tool that would allow them to assemble a quote amongst thousands of parts. I can't imagine any proper grown up database would have been the appropriate solution for this kind of work, especially since they're not connected. It's the same reason why someone might use sqlite3 in a desktop app today.

I also worked in an office full of scientists that were savvy enough with Excel but absolutely not programmers. One of them threw together a Access project to track a bunch of internal data about test results with a functional, but non-fancy forms for data entry. It kept the data clean and portable, and dumped the data out into Excel as needed. If we really needed it, we could have gotten a database installed at some expense and gotten our outsourced IT manager to back it up. But why go through the hassle?

> I'm not convinced that Access is automatically the wrong tool for every job

Neither am I (though I tend to view the idea that it is the right tool for any job with skepticism); my point above was that the particular scenario pointed out in the post I was responding to indicated that it was the "right" tool, insofar as it was, because of bureaucratic barriers to selecting certain other tools rather than purely technical suitability.

If you have a single user problem, Access is fine.

Reply on news.ycombinator.com