From: Leslie Viljoen <leslie@...>
Date: 2006-02-20T19:25:21+09:00
Subject: Microsoft Access Automation

Hey everyone

I struggled a bit on trying to get data out of an Access database using
Ruby, so I thought I'd share the
results here in case someone else needs this. I found OLEDB automation
to be the easiest method.

Excel Automation is also extremely easy and you can use the macro writer
to create much of the
VBA code that you can then adapt directly to Ruby.

One more thing - getrows returns an array that's "rotated" - I added a
"rotate" routine to my Array
class to sort that out - see the end. If anyone can improve on the
routine, please post! It looks
very much like C right now...

Les

##################################################################
# Recordset with a connection, using JET
# This works, but for some reason the memory requirements are 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 are 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:\ruby\dev\calldata .mdb'

rs.open(qry,constr)

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

##################################################################
# Recordset without connection using an ODBC source
# Program takes 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 what I used in my application - an array that can get data from
the database.
# Can you improve on this code, or offer suggestions?
#
class Array
 def readMDB(qry,dsn)
   rs = WIN32OLE.new("ADODB.recordset")
   rs.open(qry,"DSN=#{dsn};")
   rs.getrows.each do |row|
     self << row
   end
 end

 def rotate
   tmp = self.clone
   self.clear

   j = 0
   tmp[0].length.times do
     tmp.each_index do |i|
       self[j] = [] if not self[j]
       self[j][i] = tmp[i][j]
     end
     j += 1
   end
 end
end