Microsoft seems hell-bent on deprecating the swiss-army-knife of database tools. What else comes close for facading/file-swapping/cloning/name-your-acronym-connecting arbitrary database servers/spreadsheets/CSV's/flatfiles?
What weird kinds of functionality have you squeezed out of Access? And what else is there to take its place?
I think the easy answer is nothing... Access is commonly used because it is the only option and it is extensible. There is simply nothing else out there that is installed on nearly every business machine in the world as access is. If you are looking for an alternative, Oracle Application Express is a fairly powerful web based app that can be run on Oracle XE. It is a potential alternative to Access but does not support Master-Detail tables as well as access.
PostgreSQL, MySQL, FileMaker, <insert name of database that is not Access here>, Excel, custom parsers, natural language importers, Perl just because it is a swiss army knife, grep awk sed, m4, the old versions of Access before the demise of Access, ...
weird functionality? Rather than the normal myriad of ways to access Access, I use SQL statements to access Access. The SQL statements that I use work with other databases as well as Access -- weird I know.
As Mark. Access was my first approach of database and I found it powerful at the time. It has some nice features like generating SQL from "query by example". Its form features and capability to print on various format (sheet of labels for example) was nice too.
On the downside, it is proprietary, and each new version was incompatible with the previous one: if you load a base made with Access 97 with Access 2000, you can no longer load it with the older one...
Although I don't do much personal database works (list of addresses, mostly), for such work I would use either Open Office's database tool (not tried yet) or a good old open source database (MySQL, SQLite come to mind as lightweight bases) with a GUI front end, for example, SQuirreL SQL Client, and probably JasperReport as report front end.
Not as integrated as Access and with steeper learning curve, but somehow more flexible.
Now, I am sure we can find some simple good old non-relational database for the simplistic uses I had at the time. :-)
I welcome the day when Access breathes it last breath and joins the likes of Clippy.
Access is well-intentioned, but it has become a crutch. Even in large companies with able IT staffs, Access applications can run rampant, providing a pain point for knowing the global landscape when it comes to products to maintain. Linked Access databases that point at other datasources, unmaintained Access applications, and just shear flexibility are issues, in my opinion.
I think that Access is actually too powerful, too flexible, and too extensible for its own good. In Microsoft's well-intentioned attempt to bring rapid development to the desktop database realm, it really has opened a Pandora's box. Look at it from another perspective, too. Assume that a company has a few applications that are written in Access. The developer who wrote them leaves. These applications are just important enough that they still need to be used, but not important enough that IT gets the approval to port them to a more technologically capable platform.
Now, the situation is that if no one on the team knows Access, it is requirement for the new developer. This means that you might have to pass on a developer who is the most technically well-rounded and the best fit if he does not have legacy chops. I speak from experience, on this. We are down to two legacy Access applications, and are trying feverishly to convince of the needs to either incorporate the functionality into related, code-based projects or into new projects of their own. I have one developer with Access "chops", and am not going to base a candidate search on whether someone knows Access or not in the event that he leaves.
The problem with replacing Access - and replacing Access is the problem which stops me in the vast majority of cases recommending a move to Ubuntu or SUSE desktop to my business clients - is not that Access is widely used for its database facilities: it's not except with the most Micky Mouse of user-written departmental applications which are relatively trivial to re-code. The problem is the medium sized applications where the data was migrated long ago to the corporate SQL Server.
These are a nightmare. They're often badly written (I've acquired a fair few to administer over the years) and encapsulate reams of business logic. Recoding them in anything is generally quoted at a couple of man-months at the best - usually twice or three times that, and it's unusual for a department of the size these are found in to have the budget to support that. Moreover although the arrival of AJAX and good desktop-like controls has meant that this is at least now possible in theory, in practice these are of then massively integrated with the rest of the MS Office desktop and virtually impossible to disentangle with out users seeing a drop in usability in the short to medium term - which is a show stopper in itself.
I really do not know what the solution is, apart from the slow replacement of creating new systems with other methods and hoping for the gradual demise of existing apps. Trouble is I think Access could well be the Cobol of the 1990s - it'll be around for ever supporting legacy apps because it's too costly to rewrite from scratch.
As an aside, does anyone else coming from a non-Access traditional Win32 coding background have the experience of finding that the standard of coding in even professionally written Access apps is generally below average? Although superficial (but important) stuff like formatting and variable names are generally fine I find over and over again that program structuring is poor. I know that this may often be because these apps have grown like Topsy, and VBA really isn't conducive to good coding anyway, but even allowing for these factors things generally seem worse than one might expect.
Like many, I have used and abused access over the years, always felt a little dirty though ... I felt a little better about it when I came across this post by Rob Conery recently:
http://blog.wekeroad.com/blog/hacking-your-vote/
Would never have dreamed of using access in a voting system. Scary.
My cousin is a serious FileMaker guy. He seems to be doing great and has grown a small firm around it. Apparently FileMaker is a cross-platform Mac/PC system for rapid app development...
Maybe something like that will rise up with the business power-user/RAD set?
Microsoft may have a history of intentionally killing off database systems like this. I listened to a .Net Rocks interview one time with Les Pinter, where he claimed that he once heard a top Microsoft exec say that every copy of FoxPro that sells costs Microsoft thousands in lost SQL royalties. And where is FoxPro today? Officially, it is was end-of-lifed in March of 2007. So how did it get from prominance to demise? Well, Les says that Microsoft acquired it and ran it into the ground on purpose.
I am not usually big on conspiracy theories, but this does resonate with Microsoft's track record from that era.
Anyway, trivia aside, I believe there will be more RAD-style database tools... Although I personally have an aversion to using them for serious development, they empower non-developers and allow developers to solve certain types of problems very quickly.
As for the long term consequences... Well, let's just say I have seen some disaster scenarios where they didn't scale well and fragmented super-quick solutions caused big problems.
Probably the biggest/weirdest thing I did with Access was writing an EDI system from scratch. For those of you who have worked first-hand with EDI, you know what I'm talking about. What a silly idea that was. It was a big job.
I also used it for code generation back before things like Codesmith were available. It generated generate business objects (CRUD and some other basics) for ASP Classic.
I don't think MS has any intention whatsoever of getting rid of Access. They may transform it into more of an end-user tool than a programmer's tool, but it is never going away. The forking of the Jet database engine into the traditional Jet 4 version that ships with every copy of Windows (because Active Directory uses Jet 4 as its data store) and the version that is owned by the Access development group (the ACE, with its ACCDB file format, which is, de facto, Jet 4.5 or maybe Jet 5).
Access is a hugely popular and useful application and functions in a whole host of levels within any number of organizations, large and small.
Why is there no open-source alternative to Access?
Because it's way too hard to create such a complex piece of software that does so many different things well.
Access is not a DBMS. Or at least it's not just a simple DBMS. It's a very good RAD environment, a simple way to create SQL code graphically, and a regular front-end to fully fledged DBMs.
Neither SQL Server (Express or MSDE) nor Oracle, MySQL, etc. will ever replace it, until they come integrated with a simple programming language, a Crystal Reports like facility and a way for beginners to get around without having to learn SQL.
At my first professional job I developed a very big system completely in Access. Front end for the clients, admin front for me, reports and monitoring for management, permissions per user, automatic tasks run at certain times, etc. I came to learn a lot of its flaws and strengths as a result.
I've seen marvelous apps done with it, as well as pieces of crap. I still use it for personal projects, and ain't' ashamed of it (for instance, a Sudoku player, or a Karnaugh mapping implementation). There's an MVP who's created a Paint clone completely in Access, though I believe that's extreme.
Access' pearls: It's nice to easily test a database design idea and have sketch forms, reports, etc. created for you. If you change a column's name (or even a table, though that fails sometimes) it's nice to see all references to that have changed to the new name, automatically. The "sub-form" control rocks, I longed for it on VB6. And the "Thunder" button to do repeated filtering on tables is great, I wish I had something like that on SSMS!
in my experience Excel is even more widely used inside corps. We're just now doing a project where we convert ~ 60 000 Excel documents (with 4-12 sheets in each) to Sharepoint and Infopath forms. ;)
Access is just migrating to more of either a single user on a desktop or a few users on a shared database file without much security. If you want to take it to a slightly higher level, use Access as a frontend to SQL Server.
Well now it seems Access 2010 is looking to get the hooks into SharePoint in an attempt to "web enable" the Access application. There are even host sites catering to this technology. Maybe all those who were concerned Access couldn't scale can fear no more?
I doubt Microsoft will kill off Access. With Access 2007's integration with Sharepoint and the rapid growth of SharePoint, Access may in fact have a resurgence as an off-line and reporting tool for SharePoint web sites.
Microsoft would like us to move to using Office Business Applications - essentially hooking up the office apps to databases. Add SharePoint into the mix and there is a lot of possibility. Also plenty of licencing fees for MS as well.
I have seen access used to integrate and front end GIS and health data. It blew me away how well this app was coded and documented.
FileMaker is a good database for shifting from MS Access. It is a cross-platform database (mac/PC). It has a Web Viewer, through which you can connect to the web world. For example, charts, maps etc can be shown in this web viewer. FileMaker is easy to use for beginners. You could also explore the scripting mechanism and achieve data manipulation. The latest FileMaker 10 has several new interesting features. My vote is for FileMaker.
As far as the weirdest thing I've seen squeezed into Access...
I am a police dispatcher for a smaller university, and we (like almost every agency) use a CAD (computer aided dispatch) and RMS (record management system) system.
Our previous CAD/RMS software was built ENTIRELY into Access. You opened Access, and through an ugly GUI, entered calls for service, everything. Officers wrote reports through the same interface.
It worked great at first, and then as the database size grew, it became extremely slow and difficult to use. This is what happens when the state makes you go with the lowest bidder on a project...
Now we use a CAD/RMS solution that is browser-based, backed by MS SQL.
I believe File Maker Pro will probably become a new standard if people ever figure out it exists.
FMP has all of same features / short comings of Access plus you can actually make a real client / server setup if you know what you're doing.
In a single file you can define your forms, reports, tables, etc. It is also cross platform and runs on Windows or Mac, and can be adapted to web based too. All by design.
Coming from the "real" SQL servers to File Maker Pro was really hard mentally but once I got the hang of it I found it was pretty amazing. Now as a database it's nothing special but as a database application development system that "normal" people can use it really shines.
If you PLAN on a network setup I would suggest taking the time to learn how to separate the storage database from the application database up front. Otherwise upgrades require you do lots of data export / import and that can take a while or be almost impossible if your tables change significantly.
I've built a call center application that automatically handled incoming phone number lookup and automatically dialed regular POTS phones using FMP on NT. That was about 6 years ago so I imagine it's improved since then.
the nice thing about Access is its answer to large IT bloat. It comes with MS office so its already approved for use on locked down computers but I don't have to attempt to struggle for weeks/months to get an application approved through various departments, coding hours to account for, and all the testing for an application i can whip up in an afternoon with Access. Sure SQL server would be nice to use, but not worth the headache.
There is a continuum of developers in the world, rather than hard and fast boundaries. People range from business managers and IT professionals. I consider myself to be an advanced amateur developer, somewhere between the two. As such I use MS Access at work to organise a large amount of data in a small architectural office including timesheets, financials and architectural specifications. Sure, the application now is a mass of stinking p** that has grown over almost five years.
I've been searching for something better than Access for ages- I can create simple apps in VB.NET however the learning curve is huge from VBA. I've looked at all sorts of options. Often you need Crystal Reports to get any kind of reporting capability, or the IDE is non-intuitive, or linking a field to a data object takes ten minutes each time, or there is not integration with other office products at all. The boss is not going to pay for something that costs a bomb, either. I'd love to get away from Access, but nothing I've looked at gets anywhere near ticking all the boxes.
I've only used Access when I wished Excel could do a "Left Inner Join". Otherwise a MS has done a fair job making there C#/SQL offering simple (and free) to use for light weight RDB projects.
I don't think that Access is going away anytime soon. The beta of office 2010 is out with an updated Access included and the Microsoft blogs are hyping the features of Access 14 (the version after 2010) which include improved Access Projects (.ADPs) with better support for SQL Server 2005/2008 and better .Net integration.
If i were to look for a new integrated database development system providing front and backend features Oracle APEX would be the main contender. Front ends are web based requiring no runtime on the client, the whole system is free to download and instal (express edition) and given a few years the entrance barrier for new users hopefully will be reduced so it is something laymen can dabble in.
Access definitely has both pro's and cons, it's just another tool to use but not abuse. Every adult job I've ever had used ran on windows, so Access or something like it will exist. I feel sorry for the places that are stuck in Access quicksand or lost in excel hell. But are we forgetting that all that can be corrected and better yet prevented with a bad ass bi team and proper training.