# Using Ruby to get data from an Access Database

**URL:** <https://www.ruby-forum.com/t/using-ruby-to-get-data-from-an-access-database/105900>\
**Category:** Ruby\
**Created:** [June 4, 2007, 2:19pm UTC](https://www.ruby-forum.com/t/using-ruby-to-get-data-from-an-access-database/105900 "2007-06-04T14:19:59Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\
**Post date:** [June 4, 2007, 2:19pm UTC](https://www.ruby-forum.com/t/using-ruby-to-get-data-from-an-access-database/105900/1 "2007-06-04T14:19:59Z")

</div>

Hi guys, I’m very new to this Ruby language.

I only have basic experience of creating Intranet applications using ASP  
and an MS Access Database.

I’m trying to adapt some code from the [www.Alexa.com](http://www.Alexa.com) 's example code but  
don’t know if what I’m trying to attempt is out of my depth.

In its simplicisty this is what i’m trying to achieve,

connecting to an access database to return the web addresses for 1,000  
firms, using the following Ruby code to query Alexa statistics and then  
insert the result back into the access database.

#/usr/bin/ruby

require “cgi”  
require “base64”  
require “openssl”  
require “digest/sha1”  
require “uri”  
require “net/https”  
require “rexml/document”  
require “time”

ACCESS\_KEY\_ID = “— Replace with your access key id —”  
SECRET\_ACCESS\_KEY = “— Replace with your secret access key —”

action = “UrlInfo”  
responseGroup = “Rank”  
url = “[yahoo.com](http://yahoo.com)”

timestamp = ( Time::now ).utc.strftime(“%Y-%m-%dT%H:%M:%S.000Z”)

signature = Base64.encode64( OpenSSL::HMAC.digest(  
OpenSSL::Digest::Digest.new( “sha1” ), SECRET\_ACCESS\_KEY, action +  
timestamp)).strip

url = URI.parse(

```
      "http://awis.amazonaws.com/onca/xml?" +
      {
        "Action" => action,
        "AWSAccessKeyId" => ACCESS_KEY_ID,
        "Signature" => signature,
        "Timestamp" => timestamp,
        "ResponseGroup" => responseGroup,
        "Url" => url
      }.to_a.collect{|item| item.first + "=" +

```

CGI::escape(item.last) }.join(“&”) # Put key value pairs into http  
GET format  
)

print “\n\nRequest:\n\n”  
print url

xml = REXML::Document.new( Net::HTTP.get(url) )

print “\n\nResponse:\n\n”

xml.write

Any help would be greatly appreciated …

Cheers Andy

---

<div class="post-metadata">

**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\
**Post date:** [June 4, 2007, 2:57pm UTC](https://www.ruby-forum.com/t/using-ruby-to-get-data-from-an-access-database/105900/2 "2007-06-04T14:57:37Z")

</div>

Andy wrote:

> connecting to an access database to return the web addresses for 1,000  
> require “net/https”  
> timestamp = ( Time::now ).utc.strftime(“%Y-%m-%dT%H:%M:%S.000Z”)  
> “AWSAccessKeyId” =\> ACCESS\_KEY\_ID,  
> print “\n\nRequest:\n\n”
> 
> Cheers Andy

There are lots of ways to get data to and from an Access database,  
depending on whether the Access database is on the same machine as the  
Ruby script and whether the Ruby script is running on a Windows box or  
not. The simplest case would be the database and Ruby script co-resident  
on a Windows box. Then it can be done either with the OLE interface or  
ODBC – ODBC is preferable since it’s more portable.

If you need the data from the Access database for a Ruby script running  
on another box, the simplest thing is probably to write a small server  
using distributed Ruby on the Windows box and access it over the  
network. But there are other ways to skin the cat, ranging from an open  
source package called “mdbtools” that reads and writes Access files  
([http://mdbtools.sourceforge.net/](http://mdbtools.sourceforge.net/)) to more complicated things like  
“unixODBC”.

---

<div class="post-metadata">

**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\
**Post date:** [June 4, 2007, 8:09pm UTC](https://www.ruby-forum.com/t/using-ruby-to-get-data-from-an-access-database/105900/3 "2007-06-04T20:09:39Z")

</div>

> Set rsHouse = Server.CreateObject(“ADODB.Recordset”)  
> adoCon.Open “DRIVER={Microsoft Access Driver (\*.mdb)}; DBQ=” &  
> Server.MapPath(“Rent\_Management\_System\_V2.mdb”)  
> strSQL = “Select \* From House”  
> rsHouse.Open strSQl, adoCon
> 
> Any idea how this sort of thing would work with Ruby (or  
> where I could go to find out)

I use DBI with the ODBC DBD and do stuff like this:

DBI.connect(“DBI:ODBC:driver=Microsoft Access Driver (\*.mdb);  
dbq=#{filename}”) do |conn|  
conn.select\_all(‘SELECT \* FROM Pools’).collect {|row| row.to\_h}.each  
do |values|  
pools[values[‘Key’]] = values  
end  
end

- donald

---

<div class="post-metadata">

**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\
**Post date:** [June 4, 2007, 8:04pm UTC](https://www.ruby-forum.com/t/using-ruby-to-get-data-from-an-access-database/105900/4 "2007-06-04T20:04:12Z")

</div>

> There are lots of ways to get data to and from an Access database,  
> depending on whether the Access database is on the same machine as the  
> Ruby script and whether the Ruby script is running on a Windows box or  
> not. The simplest case would be the database and Ruby script co-resident  
> on a Windows box. Then it can be done either with the OLE interface or  
> ODBC – ODBC is preferable since it’s more portable.

The script would be running from the same machine as the database. I’ve  
used ADO before with Access Databases so i’d probably stick with what I  
know, bit stuck as to how I’d get the right syntax for Ruby, can’t find  
exactly what i’m looking for out there on the web…

Dim adoCon  
Dim rsHouse  
Dim strSQL  
Set adoCon = Server.CreateObject(“ADODB.Connection”)  
Set rsHouse = Server.CreateObject(“ADODB.Recordset”)  
adoCon.Open “DRIVER={Microsoft Access Driver (\*.mdb)}; DBQ=” &  
Server.MapPath(“Rent\_Management\_System\_V2.mdb”)  
strSQL = “Select \* From House”  
rsHouse.Open strSQl, adoCon

Any idea how this sort of thing would work with Ruby (or where I could  
go to find out)

Many thanks for your help

Andy

---

<div class="post-metadata">

**Author:** ![andy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/aeb1de/32.png) [@andy](https://www.ruby-forum.com/u/andy)\
**Post date:** [June 5, 2007, 1:26am UTC](https://www.ruby-forum.com/t/using-ruby-to-get-data-from-an-access-database/105900/5 "2007-06-05T01:26:19Z")

</div>

On 6/4/07, Andy R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I’ve used ADO before with Access Databases so i’d probably stick with  
> what I know, bit stuck as to how I’d get the right syntax for Ruby,

The win32ole library is your friend. This should work (almost direct  
copy of VBS code):

require ‘win32ole’  
ado\_con = WIN32OLE.new(‘adodb.connection’)  
rs\_house = WIN32OLE.new(‘adodb.recordset’)  
ado\_con.open “DRIVER={Microsoft Access Driver (\*.mdb)}; DBQ=” +  
base\_dir = File.dirname( **FILE** ) + “Rent\_Management\_System\_V2.mdb”  
str\_sql = “Select \* From House”  
rs\_house.open( str\_sql, ado\_con )