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
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 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
- 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.
Loading ...
12 Comments 
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.
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!
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?
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.
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
You call this a serious article? :s
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?
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.
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.
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.
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.
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.