What’s better than ActiveMDB?

Most anything.

For instance, here is an article on how to use MDB Tools to export everything from your Access database and get it slurped into something sane.

The only reason I develop and use ActiveMDB is that I regularly run into Access dbs that are in something loosely resembling production. I can’t get rid of them, and I don’t want to keep doing the dump-migrate-report rigamarole. ActiveMDB lets me snoop around very easily, and it lets me extract data with Ruby.

How to use ActiveMDB in Ruby on Rails

First, let me say: You probably shouldn’t use ActiveMDB in Rails. ActiveMDB is intended for exploration and for exciting action-movie narrow escapes from Access databases.


If you really, really need to, though, here’s how:

Install MDB Tools http://mdbtools.sourceforge.net/

Install the ActiveMDB gem:
gem install activemdb

Require the library somewhere. ./config/environment.rb might work
require 'active_mdb'

In a model file (e.g. ./app/models/windows_malware.rb) create a model that subclasses ActiveMDB::Base.
Set the path to the .mdb file and the name of the table.

class WindowsVirus < ActiveMDB::Base
  set_mdb_file '/var/db/windows_support.mdb'
  set_table_name 'Windows_Virises'

You can use the ActiveMDB model in your controllers much like you would an ActiveRecord model. The only find methods at the time of this writing are find_all and find_first. These methods take a hash that specifies the conditions for the WHERE clause. The keys to the hash are symbols representing the field names in the Access database. ActiveMDB will let you use downcased-underscored versions of the field names from the db. E.g. you can use :executable_name for an Access field “Executable Name”. When the field type is text or char, the WHERE conditions use LIKE with wildcards before and after the search value.

viruses = WindowsVirus.find_all :executable_name => 'virus.exe', :severity => 2

Once you have an instance of an ActiveMDB class, you can use the same Rails-like field names as methods to retrieve attributes:

return unless viruses.first.executable_name =~ /exe/

Request for .mdb files

If there are any users of ActiveMDB out there (and rubyforge seems to indicate a few downloads), I would be much pleased to hear grievous complaints about how badly ActiveMDB works.

More importantly, if you have any .mdb files you’re willing to share, I would dearly love to see them.

Drop me a line: auto matthew at g mail dot com

ActiveMDB 0.2.2 released

gem install activemdb

Improvements mostly to documentation. I used ActiveMDB to migrate an Access db to LDAP, and that turned up lots of little bitsy problems that I fixed or patched up.

ActiveMDB 0.2.1 released

sudo gem install activemdb

require 'active_mdb'

This release includes typecasting in ActiveMDB::Base models. Completely ripped off from ActiveRecord, which has been and will continue to be the theme.

In this release, you can also actually (for real) search using fields that aren’t text. The last release used LIKE ‘%string%’ for every query, which makes Booleans and numerics cry.

Much more commenting added to the code, which by the powers invested [sic] in me by Rdoc, have become documentation. Project hosted at Rubyforge.

SVN repository: svn://viewvc.rubyforge.mmmultiworks.com/var/svn/activemdb

ActiveMDB: Ruby MDB Tools on Linux and Mac OS X

This may shock my target audience, but there exist people who actually use Access, as well as other Microsoft products. In my day job, I continually acquire responsibility for more and more Access applications, and I can’t arbitrarily make my users stop using them.

It didn’t take me long to decide not to actually learn Access. Microsoft has done at least a half-decent job of making a user interface that builds interfaces to relational databases, but the tool is too powerful for the users. Some of the schemas out there make baby Matthew cry.

Plus, this was in the days Before Parallels. Which meant that I had to use Windows when to coax information out of Access. That’s not cool.

Plus also, as well, one of my projects was to build an online member directory for an entity which uses an Access app for member data in the office. Extra-whiny “That’s not cool.” I want the info out of Access and into a datastore of my choosing, and I want to use Ruby.

Google found me Brian Bruns’s MDB Tools, which lets users of sane platforms retrieve a few kinds of things from an Access .mdb file. Yes, I could have used whatever ODBC layer Microsoft provides, but that still means running a Windows box. MDB Tools may be read only, but the point is to escape from the abusive relationship, not to keep feeding it.

MDB Tools comprises a library for accessing the data and metadata in mdb files and some shell utilities for using the library. So far I have only used the tools, but I hope someday to write a Ruby extension to use LibMDB. This would require that I learn C. I have some books.

Until then, there’s ActiveMDB, available as a gem on Ruby forge. Like this:

 sudo gem install activemdb

ActiveMDB is a thin wrapper around MDB Tools that, as the name suggests, is a slavish imitation of the features of ActiveRecord that I needed that weren’t too much trouble to implement. Version 0.1.0 was usable. I used it. Don’t use it.

Not that you would, because I just released 0.2.0, which is more usable by a not insignificant margin. This version follows the pattern of SomeThing::Base class that you subclass to make models of your tables.

To use ActiveMDB, unless you collect error messages, you really ought to install MDB Tools, which may mean compile. I had some very minor difficulties compiling it on Mac OS X 10.4.x, which I will detail in a separate post. Also, I just this moment realized that ActiveMDB assumes the MDB Tools utilities are in your path. ActiveMDB relies on mdb-sql, mdb-tables, and mdb-ver.

If your mdb file is a black box, by which I mean you don’t know the table names or schemas, you can poke around a little with the methods in the MDBTools module:

 mdb_tables('Good Movies.mdb') 
   # ['Movies', 'Directers', 'Acters', 'Nude Seens']
 describe_table('Good Movies.mdb', 'Acters') 
   # [ {"Size"=>"100", "Column Name"=>"Their_Name","Type" => "Text"},
    {"Size"=>"100", "Column Name"=>"Hair_Color","Type" => "Text"}
    {"Size"=>"100", "Column Name"=>"Acter_Id","Type" => "Text"}] 

Once you know your table names and whether the tables possess anything approaching a usable primary key, you create a model class and specify the mdb file. If you don’t set a table name, ActiveMDB uses the same pattern as ActiveRecord to assume one. Right now, you only need to set a primary key if you plan to call the #count method, because mdb-sql doesn’t do aggregate functions and I totally faked it.

 class Actor < ActiveMDB::Base
     set_mdb_file 'Good Movies.mdb'
     set_table_name 'Acters'
     set_primary_key 'Acter_Id'

Then you can call the find_first or find_all method. My needs are still really simple, so there’s only one way to specify your WHERE clause, and it goes like this:

 @mal = Actor.find_first :Their_Name => 'Nathan Fillion', 
 :Hair_Color => 'Brown'
 @could_be_saffron = Actor.find_all :Hair_Color => 'Orange'

The resulting Actor instances have methods named after the columns, verbatim. Thus you’d have to call @could_be_saffron.Hair_Color, which is not cool.

The find_all and find_first methods do LIKE ‘%thingy%’ in the WHERE clause. This blows up with Booleans, so I’ve started working on some type casting (by which I mean ripping off the way ActiveRecord does it).