Access Databases, Alternatives

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....

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

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

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.....

JerAnderson wrote: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.

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.

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

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.

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.

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.

JerAnderson wrote: 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.....

I think this mostly depends on the application.

If it's small/simple and you have a clueful user using it, then a good GUI tool for interfacing with something like MySQL or MS SQL whether connecting to a remote or local server may be all you need.

If the user isn't clueful or the application is more involved, then identify whether a requirement for network access to the DB is acceptable, if so, go with your choice flavor of DB. If it's not acceptable, then unless it just can't meet your needs, something internal to the program is often the best choice as it provides simplicity on the user's end.

If you find yourself doing a lot of what looks to be repetitive development though, consider building tools to write code. My brother wrote a nice little piece a few years ago in perl that takes a simple config file and generates PHP code from it. The PHP code generates html tables from MySQL tables with several nice abilities like easily sorting the data by columns. The perl code is flexible enough to allow you to exclude/include specific data, rename the columns, customize some of the html code, add additional columns with form buttons linked to URLs generated on the fly from the SQL data, or what not, etc. Suddenly what he might blow a few hours messing around with he was cranking out in minutes on a whim.

Related to this topic...
I am a big database nut, and use MySQL and PHP mostly. For a quick UI try using dadabik (http://www.dadabik.org/). It's an easy way to get a UI up quickly for a MySQL database.

You might give Ruby on Rails a test drive. It works pretty simply with a MySQL DB backend, is well supported as a framework, and does a lot of things out of the box.

http://www.rubyonrails.org/

I prefer Java, so I have tried Trails, a Java framework that tries to be like ROR. Trails has a few warts, but you end up with a proper J2EE app when you are done.

http://www.trailsframework.org/

I think we would all be better served by a description of the types of uses you are trying to replace. Maintaining any DB / Reporting system locally is going to be problematic. We should be trying to help you get the tools you need to do the jobs you have, rather than proposing alternative ways of digging the same hole you are in. Maybe some of these apps can be pulled into the LUWS, or into MLS. Maybe we can start a side project based on the published Java Stack that you could use.

Thanks
Barrie