Access Databases, Alternatives

[andersonj]

I remember hearing a lot in recent years about an effort to greatly reduce the use of Access databases at the Church. I'm just wondering if someone might have some information to share on that, and maybe an update on how its going. Have any alternatives been identified for smaller projects that may not be important enough to garner the budget for a big web app, but are necessary nevertheless?

Access is a great tool, don't get me wrong, and I've seen some very useful systems built with it. But it seems that we had several dozen Access databases that our department was trying to maintain at one point, which shows how quickly it can get out of hand. We've since pushed back a lot on the creation of new ones, have abandoned several, and have even merged some with larger systems (such as FMAT).

It would be great to have some sort of tool that would allow people to effectively gather and share information in a way that doesn't require so many development resources. I'm sure we're not the only ones who have run into this problem....

[Moroni-p40]

A Wiki is a good way to gather and share information.

[smwfrench-p40]

You might consider downloading the community (aka free) version of mySQL and create a schema for each of your current databases.

[andersonj]

Yes, wiki's are indeed a nice way to share information - we've setup a few in my area to test the concept and found them very useful. There are so many open-source ones available, and it sounds like tools like Sharepoint might also have some sort of Wiki functionality. I'm excited to see that used even more.

However, many of the tools I've seen put together in Access get built there because their data stewards want some uniformity to the kind of data that gets gathered (information divided into specific fields, data validation, etc...). They also want to be able to run reports on that data, have the input forms resemble the paper ones they're currently using, and things like that. As far as I know that's not possible in a Wiki, so I guess I was just wondering if there had been any discussion on how to make building & maintaining tools like that easier & cheaper.

I've seen a lot of neat tools built on MySQL, too (including some of the good open-source wiki's), but even if the back end is in a bigger database engine (MySQL, SQL Server, Oracle, etc...), you still have an interface to develop & maintain. I'd love to see some sort of framework put together that developers could easily plug their database into and get a simple system up and running. Something that's about as easy as building a database in Access, but more standards based, scalable and robust. I've figured out enough on my own to get a few small websites running, and they're much easier to maintain than their Access counterparts, but there's still a lot of hurdles to get over.....

[russellhltn]

I'd love to see some sort of framework put together that developers could easily plug their database into and get a simple system up and running. Something that's about as easy as building a database in Access, but more standards based, scalable and robust.

I take it that the database that's part of OpenOffice 2.x isn't good enough? I'd think that would be the logical place to focus effort. Access is capable of interfacing with SQL. I'd think at some point OO would have the same ability or at least have that on the list of things to all.

[thedqs]

Every database has pros and cons, with Access you can almost develop an application out of it using the Visual Basic for Applications. (I don't recommend it, since for anyone to run it they need MS Access installed) But as a back end, for LARGE data, it has a slow SQL execution time. Although it is used as the back-end database for quite a few programs that need local database support. Now Oracle from what I can tell is the fastest with mySQL and other opensource projects as good for medium-large projects, but most of these are for internet based applications with a central server. I haven't done anything with OpenOffice's database program, so I can't give any input on that.

[WelchTC]

For those programming in Java, I really like H2Database. I've been using it for several projects and have found it fast, reliable and robust (especially for free).

Tom

[andersonj]

I haven't had a chance to see the OpenOffice database, but will take a look and see if it might help....

thedqs hit the nail on the head with his comment about VBA - I think the problem I'm seeing is that so many of these databases DO take advantage of Access' ability to make a full application out of any database. They then tend to get overly complex and require a lot of support to keep running.

[thedqs]

An example of over use and complication of VBA was in the mission field, we used Access to keep track of missionaries, the important financial data, baptisms, housing, etc and those programs would break at least once a week and I'd have to fix them.

Side note, the reason for the duplication in Access and in the Church programs was that you couldn't get any report from the church data that the president needed, (Types of Baptisms, sources of baptisms, financial reports, etc.) But this is for another topic/suggestion.

[dustin-p40]

I believe that the core issue here is business process control rather than technology. There are many useful technologies out there, and many would fill the requirement for tracking the data needed.

The problem is that if a separate app is created to handle each need, you soon end up with a large number of applications. Also, if the application is useful, it gets used more widely, and the ability to scale effectively becomes apparent. Most apps like this were never intended to scale to the extent that becomes neccessary. As more people use the app, you inevitably encounter some who don't use the application in the way it was intended. The end result is a maintenance nightmare.

In my opinion, when a little application starts to get wider use, or becomes mission critical, it needs to be re-evaluated. It will cost money, but if the app has really become neccessary, it should be worth it. The re-evaluation should include more rigorous interface design, business process evaluation and management approval. This can result in a true enterprise application or expansion of an existing enterprise app.

Management often won't buy off on this, insisting that the current solution works. It must be explained that the current solution works, but that soon it will not work anymore.