dcsimg

Dump Microsoft Access, Get Kexi

Billed as the "Microsoft Access for Linux," Kexi is an integrated data management application that allows you to easily work with SQLite, MySQL, and PostgreSQL databases.

Buried amongst the fray of KOffice applications is Kexi, the little application that could change your dependency on Microsoft Access or other proprietary desktop databases. The tagline on the Kexi website is “Microsoft Access for Linux.” Kexi is an integrated data management application that allows you to work with SQLite, MySQL, and PostgreSQL databases.

Upon first glance, Kexi closely resembles Access in its organization and appearance. Like Access, Kexi creates and operates on file-based databases and can connect to an ever-increasing list of remote database systems. You can create and operate on Tables, Forms, build Queries, and use Scripts with your databases within the Kexi application.

Figure 1 shows the Kexi interface ready to create databases, tables, forms, queries, and scripts.

Figure One: The Kexi Interface
Figure One: The Kexi Interface

KEXI, YOU’RE NOT ALONE

Kexi is not a standalone product. It’s part of the KOffice suite and must be used as such due to its integrated design. Lighter binary versions are available for most distributions via the Downloads page on the project website but still contain a significant chunk of KOffice.

For you purists, or those of you who can’t obtain KOffice packages with apt-get or yum, download the source tarball and compile for your platform. The latest stable release, as of this writing, is Kexi 1.1.3 (KOffice 1.6.3). The KOffice suite, once compiled and installed, assimilates nicely into Gnome and KDE. The requirements for KOffice are about 250MB of diskspace for sources and compiled programs and you need KDE > 3.2.

KEXI’S BAG OF TRICKS

Kexi is a cross-platform application and is available at no cost on all Unix-based systems. The Windows version, available here, is a crippled demo. You may purchase the fully functional Windows version for about $55 USD which includes updates and support. This fee also helps pay for development costs associated with using the Windows platform.

Available Platforms:

  • Linux
  • Solaris
  • FreeBSD
  • Mac OS X
  • Windows

Kexi is relatively secure by design since it is file-based. Moreover, there is no daemon or service running on a TCP port, so your use of SQLite and Kexi are hidden from network sniffers and scanners. Your personal databases have the same security as all your other files owned by you. Since SQLite has no internal security, shared databases require more stringent permissions to restrict access.

SQLite is a good choice for this kind of tool because it is open source and free so that it may be used for any commercial or non-commercial purpose. The free use of SQLite assures a long life for Kexi.

Kexi includes a Forms Designer that is very simple to use. All of the Form widgets are drag and drop allowing you to quickly create data entry forms. Users of similar tools will feel comfortable using this tool to create new Forms. All widgets snap to a grid or aligned in free-form fashion. Once a widget is chosen and dropped onto the Form, a Property Window appears allowing you to edit all parameters associated with that widget.

One very familiar feature to Access users is the Compact Database feature as shown in Figures 2 and 3. This feature is located under Tools on Kexi’s main menu.

Figure Two: The Kexi Compact Database Tool
Figure Two: The Kexi Compact Database Tool

Figure Three: The Compacted Database Response Statistics
Figure Three: The Compacted Database Response Statistics

KROSS

Instead of including its own programming language, like Access does with Visual Basic, Kexi includes the ability to use multiple scripting languages. Kexi uses the KDE KROSS scripting framework to provide a seamless solution for scripting language support. KROSS is not a programming or scripting language but rather a gateway to a language interpreter. Currently, only Python and Ruby are supported via plugin.

KEXI AT WORK

It’s easy to create your own databases with Kexi. In just a few steps, you can have a fully functional relational database ready to receive and retrieve data.

  • Open Kexi by using the menus or from a command line.

Note: If you are going to use scripting languages in your database work, open Kexi from the command line because any output from your scripts is sent to stdout.

Once Kexi is invoked, you are prompted to create or open an existing project.

  • Select Blank Database and Click OK.
  • Click Next on the following screen to continue creating your new database project.
  • Enter a name for your new database project (Guestbook, for example) and click Next.

    Kexy automatically names your new database with the .kexi extension.

  • Click Create to complete the process.
    Your Guestbook database is ready for tables and data.
  • To create a new Table, right click the Tables object and select Create Object: Table.

    A new table, table1, is created for you in Design View as shown in Figure 4.

Figure Fout: Creating a New Table in the Guestbook Database
Figure Fout: Creating a New Table in the Guestbook Database

  • Enter your Field (Column) Captions and Data Types.
  • To enter data into your table, switch to Data View.

If you are familiar with Microsoft Access, creating Queries, Forms, and Scripts will be an easy task. For more Kexi tutorials, see the screencasts on their website.

DESPERATELY SEEKING SQL

Athough Kexi is an interesting application, don’t uninstall Access just yet. There are several things that Kexi lacks, and desperately needs, before becoming a real contender for the Access user base.

One of the main drawbacks to Kexi is the inability to enter SQL commands (other than SELECT) into the SQL Window. To use CREATE, INSERT, UPDATE, DELETE, and others, you must use another tool or the command line interface for SQLite. You can perform these functions within the Kexi interface but, like Access, you will find this to be very inefficient.

Second, Kexi only supports Python and Ruby scripting languages. If you don’t program in either of those, then you have to opt for manipulating the database with an external scripting tool like PHP.

The third significant issue is that you cannot connect to external data via ODBC or any other method. You can only import data from an external source.

Finally, and perhaps most frustrating of all, Kexi won’t display any externally created tables. If you create tables via command line SQLite, or other tool, you won’t be able to see or manipulate them inside Kexi’s interface.

KEXI’S FUTURE

Kexi is actively developed so keep your eye out for future improvements like upcoming support for MS SQL Server and Sybase connectivity. Kexi 2.0 is also slated to include Report generation. Support for more scripting languages, such as JavaScript, through KROSS is also being explored for future updates. A Macro language, like that used in Access, is also under development.

Kexi will most likely remain tied to SQLite for the foreseeable future due to its maturity and zero configuration requirements. SQLite is also actively developed which makes it an excellent candidate for future development of applications like Kexi.

LASTING IMPRESSIONS

I usually don’t write about applications that I don’t like and Kexi is no exception. Although it has some significant shortcomings and really doesn’t deliver on the “Microsoft Access for Linux” promise, I do like it. Kexi’s abilities did surprise and impress me. I especially like the fact that I am not tied to a single scripting language inside the application. That is one of my major complaints about Microsoft Access. I really don’t like Visual Basic nor do I think it’s necessary to have a language built in to an application. Kexi does it right with plugins. Its plugin architecture means that there’s no limit to the number of scripting languages it can support. It isn’t Access quite yet but it is an exciting project that deserves notice and it did leave a lasting impression.

Comments on "Dump Microsoft Access, Get Kexi"

hedley

I would have thought that an article about a database app for Linux that compares itself to Access might make some reference to the database app that comes with Open Office, and why Kexi is or is not better. Without such a reference, I consider this article incomplete and insufficiently informative.

Reply
spikyjt

I couldn’t agree more with the above comment. Whilst I think KOffice is a great addition to the open source world, it is significantly behind OpenOffice.org in terms of development status. Kexi is particular lacking in features compared to OpenOffice Base, even though, like the rest of KOffice, it has an excellent user interface. Kexi is not ready for serious production/business use, whilst OpenOffice Base clearly is, due to its ability to connect to almost any database and its easy integration with the rest of the OpenOffice.org suite. I’m sure KOffice will get there eventually though. Hopefully by then, no one remember what MS Access is!

Reply
kzutter

How can you say “Dump Microsoft Access, Get Kexi” and “…really doesn’t deliver on the “Microsoft Access for Linux” in the same article????

And question to spikyjt: Why hope that no one remembers MS Access?

Reply
captainc

I have found Open Office Base to degrade very badly in performance for large data tables; it’s quite unfortunate. PGAdmin and web-based tools like phppgadmin are much more up to the task. Other database visualization tools that can reverse engineer the database and present ER diagrams are much more useful.

Reply
semenhyia

i don’t know about kexi nor have i used it extensively but OOo3.0 ‘s base rocks. i will stick to open office for now

Reply
vekaz

You call this a serious article? :s

Reply
jrickard

The heart of Linux application software all embodied in a single article.

1 Religion. No Linux head can even write an article without some booing and hissing about Microsoft. DUMP MICROSOFT ACCESS.

2. Followed by a glowing description of a program undoubtedly BETTER than Microsoft Access.

3. Followed by alist of all the things broken in the usual Linux app written by 19 year-old poser wunderkinds with compilers that will probably never write an app that actually works before moving on to the next thing to occur to them to do.

Once again, a great idea for a program. I’d love an Access look-alike that just acted as a front end to MySQL – a GUI report designer etc. But this isn’t one.

With the potential for profit removed from software, you get mostly broken software, incompleted projects, abandoned projects, etc.

I would hope Linux Journal would eschew sensational headlines to sell magazines, without some editorial oversite to make sure the article delivers on it. Articles about software that doesn’t work, implies that there are no GOOD programs out there to write about. Is this true?

Reply
jaikzelf

If Microsoft is so wonderful why does one have to defend it anything that good should stand on its own without comment?
Ever had to deal with a corrupted access database and even after paying a lot of money to experts had to conclude that in the end it didn’t got resolved ?
Than you might understand why people who did pay huge for the Microsoft product that did not deliver start looking for an alternative.
Microsoft never had customer service in the first place, just money making was.

Reply
foobarbaz

Microsoft Access is awful. Of course you should dump it – if you’ve got the skills to use Linux you’ve already moved to something easier like postgresql and python tkinter, or .NET and MSDE. Access is designed for middle managers whose computer skills extend to copy paste and building pivot tables in excel, and who want to learn to hack together a receivables database.

JRickard – either you don’t use Access or you don’t remember what an uncooperative bitch it is to get started with. Microsoft has put JET in “maintenance mode” which tells you how much confidence they have in it. The reason why people complain about Microsoft is because they don’t do anything well anymore, and we are forced to use their crumbling infrastructure, or one of their 3 incompatible .net versions. And please don’t whine about people dissing Microsoft when you are ready to insult the author of an opensource project as a poser – it makes you sound like a total c u n t.

Reply
foobarbaz

Sorry – didn’t mean to post that was just playing around. And I was wrong – that wasn’t what u were saying. I am a bad person.

Reply
mvirard

Because I found OO Base lacking, I gave Kexi a try … and it failed. From an XP client, I attempted to open a MySQL 4 database (a Koha 2.2.9 DB) on a Debian Linux server. It could not interpret about 5% of the Koha tables.

End of test.

Reply
bcspratt

Hi Ken,

Thanks for the article, I liked it. I had not really heard about kexi and it is nice to know open source is addressing the MS Access market. Knowledge is power, and if enough developers get interested in kexi the community will continue to polish it till we get it right. It might even be better than MS Access.

Reply
marytee

I thought the article was little confusing in its original premise: Dump Access for Kexi, because at the end, Ken Hess talks of the significant shortcomings of Kexi. Despite eschewing nearly all things Microsoft in my personal life, I still use Access to create small apps for non-profits, etc. Neither Kexi or its OpenOffice counterpart, Base, have Access’s robustness. I’ve donated to the OpenOffice project in the past. Perhaps I should throw a little more cash at it to move Base along.

Reply
wizkid

When I saw the headline “Dump Microsoft Access, Get Kexi”, I got really excited. The excitement didn’t last long …

What a dumb article!

Reply
mirv

I use Gnome Edit for web database development (PHP/MySQL) having long since abandoned Microsoft Access as my principal database hobby. I also develop my own custom (task-specific) database engines using Borland Delphi. However, one thing that Microsoft Access has always had going for it is that it allows me to develop simple solutions quickly and with little fuss. There’s a point at which an Access application becomes a pain in the butt, but I’m yet to come across an open source package that re-creates that bridge from a simple problem to simple solution, and there will always be lots of simple problems that only need simple solutions that Access is well suited to. Linux has had plenty going for it in the big end of town for a long time, but it’s nice to come across a project like Kexi that seems to be targeting the KISS principle in the traditionally geeky Linux world. I’m not really on the band-wagon of trying to get the world to abandon Microsoft in favour of open source, but for those who are, most of the world currently (for better or worse) uses Microsoft products so that is what they know (there is no point arguing about whether it should be that way – it just is), so if you want to win them over, you must (for better or worse) make your programs feel like Microsoft equivalents. Microsoft dominates because it has marketing experts selling its software and they target what most users care about; aesthetics and user-friendliness. If you achieve this, then your product is competitive. Even if Kexi becomes a drop-in replacement for Microsoft Access, trying to sell it on its technical merits is a waste of time because most end-users and managers don’t understand and don’t care (most wouldn’t have even heard of linux-mag.com so they wouldn’t have read this article). When Kexi is easy to use, looks cool and is cheaper than Access, then sell it based on that, using media that average people are exposed to (not Linux forums and magazines). As long as the software works most of the time, it doesn’t have to be technically perfect. Microsoft products never are and companies fork out big bucks for them.

Reply
grabur

@mirv agree.

It’s pretty sad that there isn’t a free desktop db app that rivals access in 2010. I’ve been involved in relatively large projects using MS Access. If it worked as you’d expect it would be fantastic.

Microsoft were lucky to plug this niche early (even if it has warts.)

I’ve been seeking a drop in replacement for Access for a long time. And looked at OOo longingly. But the last time I tried it (pre version 3) it still wasn’t fit for purpose. I tried Kexi in it’s previous life, and it was promising, I can’t remember the name of it then.

I also think you have to be really careful labelling a product an MS office replacement if it also isn’t fit for purpose.

I dread opening OOo. I tout it as an office replacement, and always say, ‘it’s not as good as MS office, but at least it’s free.’ Now we have the farce of OOo being forked, I only hope it helps.

I think there is still a dearth of fine linux SMB desktop apps. I’d pay £50 for MS office any day of the week if I could run it on Linux. And I’m an MS hater!

Reply
grabur

Getting back to the article. It was poorly titled. Pathetic and sensational, which appears to be an ongoing trend for linux-mag.

I appreciate the author’s honest appraisal of the software. Told me what I wanted to know: Is Kexi a drop in replacement for MS Access? No, not yet, but watch this space.

Reply

I don’t even know the way I ended up right here, however I thought this put up used to be good. I do not recognize who you might be but certainly you’re going to a well-known blogger if you happen to are not already. Cheers!

Reply

My brotheг recommendeԁ I might like this web site.

He was entiгely right. This post tгuly made mу dаy.
Yοu can not imagine just how much time I haԁ spent for this infοгmation!
Thаnks!

Reply

Really enjoyed this article post.Really looking forward to read more. Will read on…

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>