Microsoft Access Automation

[Leslie Viljoen]

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] = tmp[j]
end
j += 1
end
end
end

 

[William James]

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...

Use Array#transpose.

 

[Leslie Viljoen]

Leslie Viljoen wrote:



Use Array#transpose.

Good. I really did search and search before writing my own - and there
you go, I just didn't look properly.
I'm so glad I always ask.