MS Access Alternative?

**Tundro Walker** · April 4th, 2008

I was digging around for an MS Access-like db for Linux, too, and I could have swore there was some KDE equivilent. Maybe that's Kexi, which you've been mentioning?

I really hate to do this, but as much of a pain in the butt as MS Access can be at times, you're probably better off sticking with it for now. It's very flexible, and provides a decent common ground for beginner's and advanced users.

I think what I would do in your situation, though, is try to farm the back-end database tables off onto like MySQL or MS SQL Server, or Oracle, then use MS Access as a thin-client to store all the forms, queries, etc. Instead of linking in the server tables and making queries from them, opt for pass-through queries, which run much faster since they're done server side. Access' query engine is notoriously slow sometimes with linked tables.

So, you'd have this back-end data server, and each person would have their own copy of the MS Access db client, which they could bloat up with their own queries and such. You'd keep a main development copy of the Access db, which you'd make the base changes to common forms and things to, then everyone can just import the updated common forms, queries, etc. You can even do up a macro or VBA script that would delete all the old common stuff before re-importing it, that way folks don't get stuck renaming things to get the "2" off the end.

If you're strapped for developer resources, then MS Access is a low-cost solution. However, due to its limitations, it's really just for small-scale db operations. If you're just using it with 10 folks, then no problem. You could even use on MS Access db as your "back-end" to store the tables everyone's client-side version links in to. You just need to make sure it doesn't get too bloated. MS Access has a nasty habit of "going critical" if it reaches like 500mb or such. The db can crash, and you might be able to compact/repair, but more likly you end up having to import all the stuff into a new MS Access db to recover from it (had a guy at EDS that this would happen to a lot until I broke his main MS Access data db into sub db's that each stored chunks of tables ... the developers wouldn't let us turn it into a SQL Server table structure, so we were kind of limited on our options.)

Anyways, make sure you untick the "Track changes" and stuff, too, since that's been known to corrupt an MS Access db over time.

I'd say just stick with it, and use MS Excel as your output / reporting tool. I personally automate MS Access db's with VBA to tap into various data sources (SQL Servers, Oracles, etc) compile data, spit out MS Excel reports with pivot tables, save it some where, then automate MS Outlook (using Redemption .dll to avoid the security pop-up) to email the reports to folks.

That trio is a very good, low-budget office automation grouping if you're lacking real software developers to make things more robust and large scale.

You're right, though. It'd be nice if Base would finally catch up to Access. I'd like to really mess around with using an MS Access-like db that uses the simpler Python scripting instead of jacking around with VBA all the time.

So, I guess if you had the choice, rely on in-house software development to build & develop your solution, or see if the budgeting and tech departments can help you purchase a third-party tool along with a service level agreement stating the third-party will help you implement, upgrade and keep it going. Using a third-party tool means it's faster to implement, but it may not have all the bells and whistles you want.

If you're stuck with a low budget and very little development resources, just stick with what you know. If the MS Access db you create turns into something the company can't live without, they'll eventually figure out a larger-scale replacement for it soon enough. But for now, at least you'd be in control of developing it and getting it running just the way you like (which can be good if you're good with MS Access, or a nightmare if you're not so good with it.)

**mariuz** · April 16th, 2008

i agree with the firebird post from above
an good openoffice+firebird backend can replace access and mssql anytime , here is the howto for firebird + openoffice setup

http://jobinau.googlepages.com/OOFB.htm

another guide here
http://www.firebirdnews.org/?p=1589

**Tundro Walker** · April 17th, 2008

Forgot to mention the main annoyances with MS Access ...

1) If you have folks using different versions of MS Access tapping the same db (front-end, for instance), then the folks with the highest MS Access version will end up setting the MS Access code reference to their version, and folks with lesser versions logging in will get an error saying "can't find the MS Access code library."

EG: Had some folks at one job using MS Access 2003 (MS Access version 10 lib). I did a db in MS Access 2000 (MS Access version 9 lib). They got into my db to do some stuff, then I tried getting in, and it told me I didn't have the version 10 lib. Turns out their more recent version of MS Access "upgraded" the lib reference, with no reference to the old lib. So, I had to find some really annoying jury-rigs around this. (Basically had to make different version 10 & version 9 front-ends for whomever was using the different versions of MS Access).

2) Some folks end up with a "crippled" MS Office installation that can't run advanced VBA code. You can test for these setups easily by making a small VBA function that simply does ...

Code:

function test_msg()

 msgbox currentdb.name

end function

If it gets an error code, then they have a hobbled VBA install. Error message usually says something like the .dll isn't found or something. It might be that their version is having a hard time re-referencing the lib like I mentioned above.

In any case, both of these are very annoying, because you might develop some good stuff that works on your comp just to find out it fails miserably on other peoples machines.

I think those are two very solid points for looking into alternate db solutions than MS Access. (Stupid Microsoft keeps renaming the *** **** lib every version of Access they dump out ... *grumble-grumble*)

**Jenske** · May 12th, 2008

I've been working with Access for about 15 years now (on Windows). At home and since about 4 years, I've been exclusively working with Ubuntu.
I _HAD_ some databases, "written" in MS Access, that worked perfectly for me, so I was very interested in any good db-system that would more or less have the same possibilities as Access.
Unfortunately, until now and to my opinion, there is none. Base is quite good, but only recently it is able to alter field names or field order in the database _without_ deleting all data (sic!).

Everyone on several forums said "they" were working on it, but it's pretty damn annoying if, having installed this or that database system, having tried to simply copy data from my old MS Access data to this new system and having to discover that ...
-- the database explodes in size (base makes a database about a zillion times the size of a spreadsheet containing the same data)
-- OO Base works with my data, but works extremely slow (worse than using a Commodore 64 ???)
-- Kexi is still in its developmental stages

_BUT_ since several months there have been major improvements to db-systems that work in Linux. I've tried Kexi, OpenOffice base, even Tellico. Depending on what you need, I'm pretty sure these days it _IS_ possible to use these--especially OO Base--for 'normal' home databases.

Biggest problem: I've no clue on performance in a multi-user environment.

Based on my current experience I'd set up a database like this:
-- have data stored in a MySQL system ('backend')
-- address data from MS Access or Base ('frontend')

**Mr. Picklesworth** · May 12th, 2008

Glom is one really interesting database tool, although a tad different from Access. Extremely flexible, of course, and quite useful

It's in the repositories, via Add / Remove Programs. Unfortunately, the version packaged with previous Ubuntu releases has generally been a bit ugly. If you are running Hardy, though, it should be quite satisfying!

**Crick** · February 21st, 2009

Originally Posted by hizaguchi

I've done some more research and I think what I need is a PostgreSQL database

Absolutely. It's an extremely sound choice for a typical business database, especially to replace an MS access backend. Security, data integrity, flexibility, speed, it has it all. I use it, and it's brilliant. The user community is wonderful.

I looked into the choice of frontend two years ago. I'm not sure glom existed then. Unfortunately, nothing had the speed and convenience of application development that Access has. I didn't want to cripple my users with a web front end - the speed would be too slow for lots of data entry.

Probably the biggest thing missing for me with the alternatives was the ease of designing forms with subforms. I use that all the time and I didn't find it in the FOSS offerings.

There are a lot of things you have to do to workaround issues with Access and Postgresql, but most can be worked around I've found. Let me know how you go with Glom.

**Mr. Picklesworth** · February 21st, 2009

May be worth trying Glom 1.8, by the way. It's in the Openismus PPA.
Lots of bug fixes

(Although it also needs an "unofficial" backport of libgoocanvas, which other apps should be fine with but we can never be sure, so use at your own risk).

**7penselen** · February 27th, 2011

I'm needing a good and userfriendly database frontend.

I tried OOBase 3.21 which is really not working well and it misses any sense of logic. Esspecially the form designer. This program costs a lot of time without getting real results. Oldy MS-Access 2000, which I've used 10 years ago works 10 times better

Kexi 2.2.2. This program is not ready and cannot link several databases well. I think this program has potential but I need it now and not over 2 years.

and finally i tried GLOM didn't startup.
Not the repos and not
openismus-team-ppa-maverick.list

~$ glom
ConnectionPool::get_and_connect(): m_backend is null.
**
Gtk:ERROR:/build/buildd/gtk+2.0-2.22.0/gtk/gtkrecentmanager.c:1942:get_icon_fallback: assertion failed: (retval != NULL)
Aborted

Is their a database frontend with reasonable features like linked forms of linked databases which are working? I'm more than a week busy without any result? Is their a web-based on PHP tool I can install on my computer who can make forms with subforms?

I use Kubuntu 10.10 on a eeePC and on my main PC.