RubyGarden Archives: Scripting Access

Editor's Note: Once upon a time, there was a website named RubyGarden.org, which contained many helpful links and articles. That website has recently disappeared. The following "Scripting Access" article was salvaged from the Google cache and is provided here in its entirety.

---

Here is a quick example on getting data from a Microsoft Access database.

Recordset with a connection, using JET. This works, but for some reason the memory requirements were large. Using an ODBC source for the same data seems to use almost no memory!

require "win32ole"

conn = WIN32OLE.new("ADODB.Connection")
conn["Provider"] = "Microsoft.Jet.OLEDB.4.0"
conn.Open('c:\ruby\dev\calldata.mdb')

rs = conn.Execute("select date, dialednumber, extension, cost from
    callrecords where call = 2 and date >=#01-jan-2005#")
rs.getrows.each do |row|
    puts row
end

conn.Close

Recordset without a connection, using JET. This works, but for some reason the memory requirements were large. Using an ODBC source for the same data seems to use almost no memory!

require "win32ole"

rs = WIN32OLE.new("ADODB.recordset")

qry = "select date, dialednumber, extension, cost from callrecords where
    call = 2 and date >=#01-jan-2005#"
constr = 'Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\data\calldata.mdb'

rs.open(qry,constr)

rs.getrows.each do |row|
    puts row
end

Recordset without connection using an ODBC source. My program took about 28MB mem vs. 39MB for the JET version. The difference is massive when working with a lot of data Requires that you create the ODBC data source!

require "win32ole"

rs = WIN32OLE.new("ADODB.recordset")
qry = "select date, dialednumber, extension, cost from callrecords where
    call = 2 and date >=#01-jan-2005#"
rs.open(qry,"DSN=calldata;")

rs.getrows.each do |row|
    puts row
end

Here's a function you can drop at the top of your program that alters the definition of Object. It will work for loading access data into any object class that supports "<<" and "transpose" - such as Array. Transpose is required since the recordset comes back with the rows "rotated".

public
def readMDB(qry,dsn)
    rs = WIN32OLE.new("ADODB.recordset")
    rs.open(qry,"DSN=#{dsn};")
    rs.getrows.each do |row|
        self << row
    end
    self.transpose
end

Usage example

qry = "select date, dialednumber, extension, cost from callrecords"
dsn = "DSN=calldata;"

a = []
a.readMDB(qry, dsn)
p a

Question: Is there a way to use paramters on the query like the ones from DBI? E.g.: (note the '?')

select * from callrecords where call = ?

---

Using Ruby & ADO to Work with MS Access Databases

A reader in the Ruby forum recently asked about using Ruby to get data from a Microsoft Access database. This can be done easily using the ActiveX Data Objects (ADO), via the win32ole library. Let's walk through it...

As usual, require the win32ole library:

require 'win32ole'

Next, create a new ADODB Connection object, which will manage the connection to the database:

connection = WIN32OLE.new('ADODB.Connection')

To open a connection to the Access database, call the Connection object's Open method, passing it the Provider and Data Source (your Access mdb file) parameters:

connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;
                 Data Source=c:\baseball\lahman54.mdb')

To execute an SQL statement that does not return a recordset (ie, CREATE, ALTER, INSERT), you can call the Connection object's Execute method:

connection.Execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")

To perform a query that returns a recordset of data, first create a new ADODB Recordset object:

recordset = WIN32OLE.new('ADODB.Recordset')

Then call the Recordset object's Open method, passing it the SQL statement and your existing Connection object:

recordset.Open(sql, connection)

This loads the query results into the Recordset object. The Recordset object's GetRows method returns an array of columns (not rows, as you might expect), so we'll use the Ruby array's transpose method to convert it to an array of rows:

data = recordset.GetRows.transpose

Close the Connection object by calling its Close method:

recordset.Close

We could create a simple class to wrap up this functionality:

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

This class is very similar to an SqlServer class I previously discussed.
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

That about wraps up our show for today. As always, feel free to post a comment here or email me with questions, comments, or suggestions.

Thanks for stopping by!

Ruby, Excel, and SQLite (instead of MS Access)

I used to rely on Microsoft Access for my desktop database needs, but have recently converted to SQLite and its associated Ruby library. SQLite is fast, light, and powerful. Combining SQLite with Ruby gives me the ability to quickly handle most data processing tasks.

Example: Let's say you've been given an Excel workbook with multiple worksheets of data, and you need to cross-reference data between worksheets. In many such cases, you'll want to import the data into a real database and use SQL.

Here's a brief, unpolished snippet of code that reads data from an open Excel workbook and creates an SQLite database with a table for each worksheet in the Excel workbook:

require 'win32ole'
require 'sqlite3'

#   Connect to a running instance of Excel
xl = WIN32OLE.connect('Excel.Application')
#   Get the active workbook
wb = xl.ActiveWorkbook
#   Create the SQLite3 database
db = SQLite3::Database.new('excel.db')
#   Create a database table for each worksheet 
#   in the workbook
wb.Worksheets.each do |ws|
    #   Grab all values from worksheet into a 
    #   2-dimensional array
    data = ws.UsedRange.Value
    #   Grab first row of data to use as field names
    field_names = data.shift
    #   Create database table using worksheet name and 
    #   field names
    db.execute("CREATE TABLE [#{ws.Name}] \
        ( #{field_names.join(',')} );")
    #   For each row of data...
    data.each do |row|
        #   ...single-quote all field values...
        row.collect! { |f| f = "'" + f.to_s + "'" }
        #   ...and insert a new record into the 
        #   database table
        db.execute("INSERT INTO [#{ws.Name}] VALUES \
            ( #{row.join(',')} );")
    end
end

Now, you've got your data loaded into a real database, quickly and easily, and can get to work on it... via Ruby or the SQLite command line interface.

I would be happy to provide more info on using SQLite3 with Ruby, but why the lucky stiff gives a pretty good jump-start here.