# Microsoft Access Automation

**URL:** <https://www.ruby-forum.com/t/microsoft-access-automation/54012>\
**Category:** Ruby\
**Created:** [February 20, 2006, 11:27am UTC](https://www.ruby-forum.com/t/microsoft-access-automation/54012 "2006-02-20T11:27:14Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![leslieviljoen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/3d9bf3/32.png) [@leslieviljoen](https://www.ruby-forum.com/u/leslieviljoen)\
**Post date:** [February 20, 2006, 11:27am UTC](https://www.ruby-forum.com/t/microsoft-access-automation/54012/1 "2006-02-20T11:27:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![leslieviljoen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/3d9bf3/32.png) [@leslieviljoen](https://www.ruby-forum.com/u/leslieviljoen)\
**Post date:** [February 20, 2006, 12:54pm UTC](https://www.ruby-forum.com/t/microsoft-access-automation/54012/2 "2006-02-20T12:54:57Z")

</div>

Leslie V. wrote:

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

---

<div class="post-metadata">

**Author:** ![leslieviljoen](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/3d9bf3/32.png) [@leslieviljoen](https://www.ruby-forum.com/u/leslieviljoen)\
**Post date:** [February 20, 2006, 1:28pm UTC](https://www.ruby-forum.com/t/microsoft-access-automation/54012/3 "2006-02-20T13:28:14Z")

</div>

William J. wrote:

> > Excel Automation is also extremely easy and you can use the macro writer
> 
> 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.