News for hackers. Stuff that matters.
Re:Excel and SQL by mattmanser on Thursday May 23, 2013 @05:24PM (#5759611)
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.

Re:Excel and SQL by jacques_chester on Thursday May 23, 2013 @08:44PM (#5760550)
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.

Re:Excel and SQL by untog on Thursday May 23, 2013 @09:13PM (#5760666)
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.

Re:Excel and SQL by mistermann on Thursday May 23, 2013 @06:53PM (#5760069)
> 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".

Re:Excel and SQL by fusiongyro on Thursday May 23, 2013 @07:48PM (#5760314)
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.

Re:Excel and SQL by jpitz on Thursday May 23, 2013 @08:39PM (#5760529)
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.
Re:Excel and SQL by jrarredondo on Thursday May 23, 2013 @10:41PM (#5760974)
Actually, in the latest Office 2012, Access apps are now three tier apps with SQL Server 2012 or SQL Azure backends.
Re:Excel and SQL by panderon on Saturday May 25, 2013 @12:22PM (#5767675)
Point of clarification, concurrent transaction count is limited to 255. http://office.microsoft.com/en-us/access-help/access-2010-sp...
Re:Excel and SQL by moron4hire on Thursday May 23, 2013 @09:52PM (#5760800)
JET isn't all that bad. It can be used to run SQL queries CSV files. As an ETL tool it's pretty flexible.
Re:Excel and SQL by fusiongyro on Friday May 24, 2013 @10:12AM (#5762852)
It's amazing how much things change when you're not paying attention. Thanks for explaining this.
Re:Excel and SQL by goofygrin on Thursday May 23, 2013 @09:51PM (#5760798)
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.

Re:Excel and SQL by slg on Thursday May 23, 2013 @06:03PM (#5759812)
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.
Re:Excel and SQL by jiggy2011 on Thursday May 23, 2013 @06:31PM (#5759947)
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.

Re:Excel and SQL by mattmanser on Thursday May 23, 2013 @08:03PM (#5760380)
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.

Re:Excel and SQL by regularfry on Friday May 24, 2013 @06:58AM (#5762095)
Bringing proper development tools to Excel is the single biggest thing Microsoft could do to undo decades of damage Excel has caused.
Re:Excel and SQL by rz2k on Thursday May 23, 2013 @05:56PM (#5759770)
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.

Re:Excel and SQL by dragonwriter on Thursday May 23, 2013 @07:00PM (#5760103)
> 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.

Re:Excel and SQL by chaz on Thursday May 23, 2013 @08:39PM (#5760530)
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?

Re:Excel and SQL by dragonwriter on Thursday May 23, 2013 @08:46PM (#5760561)
> 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.

Re:Excel and SQL by Spooky23 on Thursday May 23, 2013 @08:51PM (#5760587)
If you have a single user problem, Access is fine.