DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

David has posted 23 posts at DZone. View Full User Profile

Using Ruby & ADO With MS Access Databases

06.17.2007
| 10099 views |
  • submit to reddit
        From the <a href="http://rubyonwindows.blogspot.com">Ruby on Windows</a> blog.

A simple class for working with Microsoft Access databases via win32ole and ADO.
class AccessDb
    attr_accessor :mdb, :connection, :data, :fields

    def initialize(mdb=nil)
        @mdb = mdb
        @connection = nil
        @data = nil
        @fields = nil
    end

    def open
        connection_string =  'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
        connection_string << @mdb
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        recordset = WIN32OLE.new('ADODB.Recordset')
        recordset.Open(sql, @connection)
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            @data = recordset.GetRows.transpose
        rescue
            @data = []
        end
        recordset.Close
    end

    def execute(sql)
        @connection.Execute(sql)
    end

    def close
        @connection.Close
    end
end
We would use this AccessDb class as follows:
db = AccessDb.new('c:\Baseball\lahman54.mdb')
db.open

db.query("SELECT * FROM AllStar WHERE playerID = 'conceda01';")
field_names = db.fields
rows = db.data

db.execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

db.close

Further details can be found <a href="http://rubyonwindows.blogspot.com/2007/06/using-ruby-ado-to-work-with-ms-access.html">here</a>.