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'
end
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).
The electrofriend 