Is MS Access still the most efficient RAD tool for small-scale custom apps?

Question

Of the many other development tools I've used, nothing holds a candle to the 'Functionality to Development Effort' ratio of MS Access.

The reason I am asking is that I have been out of the language selection process for a few years, working on a large .Net system, and am only anecdotally familiar with the latest development tools outside the .Net world.

I'm well aware of the limitations of Access, but for a limited concurrency (usually only 1 user at a time), small business, custom app, has anybody found a comparable end-to-end solution or combination that comes close?

It doesn't have to be free, open source, or even Windows based. It just has to allow the same speed of development and maintenance, and maybe even provide some additional amenities like seamless autointegration with a server-based DB Engine (like Access does with its own 'Jet' dbms), better web support, and a file format more compatible with source control.

I don't want to miss out on anything. Please share your development experience with your suggestions. Thanks.

Answer 1

While your tags and questions pertain to Access, this likely going to give you a bit of pro biased words towards Access itself

However, one product could be access 2010? After all, old versions tend to be the best competitor to new versions.

You mention the native ability to work with SQL server. You are aware that in Access you can create what is called an access data project. These data projects are 100% native ole DB connections to SQL server. That means when you create and design queries or use the table design relationships etc., you’re sending DDL commands directly to SQL server. So, none of the data is stored locally in Access. As a result of these Access Data projects are scalable in terms of number of users, and yet you use a familiar design paradigm to develop your application with, but all of the queries run natively on SQL server. (it is a oleDB connection).

So, you can now use Access as a native client to SQL server. I should point out however that most developers prefer simply using linked tables to SQL server (but this means you use the SQL server management tools to design and build the tables). However, if you don’t want to use or learn sql server management studio, you can run sql 100% from access if you use a access data project.

So you have 100% integration option with SQL server if you want.

The next issue you brought up was that of source code control. The source code control add-in for Access does allow you to use VSS. So, you can use source code control for Access, and we’ve been able to do so for probably last 5 versions. This means you can have concurrent developers. Each developer can thus check out parts of the application and work independent of other developers. They thus each their own local build on each of their desktops. I should point out that Access is capable of distilling any of its objects from reports to code to forms out as separate text files and it’s this capability that allows source code control to work.

You also mentioned better web support? Access 2010 has full web site creation ability. Here’s a video, and if you look at the halfway Point I switch to 100% running this application inside of a browser. All of the forms, sub-forms, reports and even continues forms work in any web browser (VBA does not run in those forms, but a new macro language does). http://www.youtube.com/watch?v=AU4mH0jPntI

The new access web services run on SharePoint 2010 (hosted options are available).

The one other thing to point out that the new jet engine, now referred to as Ace has stored procedures and data triggers at the table level. So I just wanted to point out, that you have stored procedures and triggers at the engine level now (even when building desktop only applications). So, you have source code control, you have a native SQL server option, and you have full web creation ability. We also now have a 64 bit version of VBA and a 64 bit version of jet (ace) the data engine for Access 2010.

So, perhaps one product to compete with what you have now is access 2010? While all of the features I spoke of been in access for a long time, the web building ability, and the engine level triggers and stored procedures is new for 2010.

Answer 2

I would say there aren't any alternatives that compare 1:1 with Access for features and functionality. But compared to 5-10 years ago, the higher-level tools seem to me to be getting better at offering ease in some of the things that they didn't use to offer (such as data-bound controls, grids nearly as easy to use as Access datasheet/continuous forms, report writers almost as good as the Access one (though in that case they're chasing a moving target, as the report writer got great upgrades in A2007)).

From where I sit, FileMaker is the only close comparison, but it lacks native SQL (though it can connect to SQL database engines) and its scripting language is not nearly as extensive, powerful and extensible as VBA. It does have web components that allow web deployment, but A2010 catches up with that (and perhaps exceeds it -- I've never seen a web-enabled FM app, to be honest). As to source control, I don't know that there's anything for FM at all (I don't know how it stores its code or what kind of IDE it has or anything -- I really only know it by reputation, as I never had the time to really test any of the downloaded demo versions).

I think you have to ask yourself what the relative importance is to you of the things that Access does well and the things it barely does in comparison to the alternatives. You can have excellent source control with higher-level development tools, but at the cost of many of the RAD features and data integration of Access. As someone with experience with .NET, you're not going to have a learning curve on using that for database development, so I wouldn't toss that out as a possibility.

Last of all, you're much too hard on Access/Jet/ACE by saying it's limited to a single user. That's just not true and never has been. Sure, it's quite possible to engineer an Access app such that it bogs down with two users, but you've got to be doing just about everything wrong to have that happen. One thing that many people mistakenly do (I did it in my first significant multi-user app) is to think they need to use pessimistic locking. This just makes everything a lot harder -- Jet/ACE does a pretty good job of avoiding edit locks/collisions when it can if you use optimistic locking. I've used it exclusively in all my apps after that initial experience trying to make pessimistic locking work, and I've had no issues with user populations into the 20 simultaneous users range. Once a user population is in that range, you have to start being a lot more careful with your Access app and at that point I'd consider upsizing to a server back end, anyway. The key point about that, though, is that the things that make client/server efficient also make an Access/Jet/ACE app more efficient (the main principle is to retrieve the smallest amount of data possible to serve the users' immediate needs). The main difference is connection control. Jet/ACE works best with an always-open single connection per user, while client/server penalizes you for that (because you're stepping on the database server's job, i.e., determining how to use shared resources).

Anyway, I'm just rambling now.

From my perspective as an Access developer of 14 years who has looked longingly at other development platforms forever, nothing replicates the full set of features and functionality that are in Access. And with the direction that has become clear with A2007 and A2010, the future of Access looks very bright to me. I just don't see any competition that's going to be better than Access coming down the pike any time soon.

Answer 3

Take a look at lyterad, its meant to be a lightweight RAD platform for custom db apps, though its still in early phase it does all that you mentioned.