Monday, June 4, 2007

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!

Digg my article

32 comments:

Charles Roper said...

Awesome, this blog gets better and better. This kind of information is really sorely lacking out there in the Rubysphere and you're filling that gap nicely. Kudos.

David Mullet said...

Thank you very much, Charles!

David

Anonymous said...

I guess Charles says it all very well. Not all, but he tries. :o)

I really think we are missing the message in this post. The power of Win32 OLE has been leveraged here. It's not just accessing DBs. It is using OLE.

Daniel Spiewak said...

So, what about an ActiveRecord provider for Access?

Anonymous said...

It's possible to use ADODB.Connection to work wiht excel files like this python example:
http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/440661

David Mullet said...

@Daniel:

I haven't seen a Jet (MS Access) database adapter yet. I know that an SqlServer adapter exists, though. If it utilizes ADO, it may be possible to modify its connection string to work with Jet databases. Perhaps I'll look into it, if no one else has done so; but I don't know how soon that could be.

Anonymous said...

Dont forget that you need set sql value. In fact that value is the one that is in connection.execute. On the top of the page. connection.Execute("INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');")
You can change it by:
sql = "INSERT INTO HallOfFame VALUES ('Dave', 'Concepcion');"
I hope this will be usefull. Bye

Anonymous said...

Thanks for the hint in using the powerfull 'win32ole'.
For my needs I tried to use AdoX with Ruby to replace spaces in Access field names, and it worked fine. Here is the code:

# SpaceRep.rb
require 'win32ole'
connection = WIN32OLE.new('ADODB.Connection')
'Data Source = is your Access file
connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=users.mdb')
cat = WIN32OLE.new("ADOX.Catalog")
cat.ActiveConnection = connection
print "Inform your table name: "
yourtable = gets.chomp
print yourtable, "\n"
# loop through each field in the table replacing the Spaces by what you want - in my case "_"
for col in 0..cat.Tables(yourtable).Columns.Count - 1
cat.Tables(yourtable).Columns(col).Name = cat.Tables(yourtable).Columns(col).Name.gsub(" ", "_")
end
print "Task completed! Check your database."
gets

Anonymous said...

Sorry, please correct the above line to be a ruby comment starting with #, as following:
# Data Source = is your Access file

Patrick Wright said...

Thanks. This post just saved me several hours. I'm a big fan of code which integrates between open source and 'closed' systems, and this particular wedge allows me to crack a closed system's data wide open.

David Mullet said...

You're welcome, Patrick!

"This post just saved me several hours."

Another Ruby on Windows success story!

[grin]

David

daniel said...

A Microsoft Access adapter for Rails has been made (Rails MS Access Adapter).
It probably won't work with the latest ActiveRecord, as it's not an adapter gem; though with older versions it works great.

John said...

Very nice posting. I got a software stellar phoenix access repair which repair and recover corrupted mdb database and MS access files. It is a powerful and robust software and easy to use.

Thanks

Anonymous said...

Wow thanks for this post! It is really helpful.. best regards, nikolai

Unknown said...

It just works. Thank you!

Anonymous said...

Can we have the something similar for conenction to Oracle dB.

I want something other than DBI to connect to my Oracle 10g dB.

Anonymous said...

thank you very much, this helped me very much. Otherwise i would have to do quite a bit of work in vb ...

Joey said...

Thanks for the post!

However I'm a bit confused about this... I'm working on porting my code over from vbscript to ruby. In VB, I'm used to referring to a certain field within the recordset using

value = recordset("fieldname")

It seems that, using your class example, after running a query to return a recordset, I would have to refer to a certain field within a recordset by an index number (ex. recordset[0]) instead of a field name, as I do in vb. Is there an example that shows how to refer to a single value within a recordset by it's field name, instead of an index number?

Joey said...

Easy enough. Should have looked at the ADODB API first.

value = recordset.Fields("FieldName").Value

Anonymous said...

Hi,

Thanks !

Question:
How to see the updated records,
If you update something.

say:
db.execute("update table1 set name='myname' where id=1 ;")

query after this still returns the old values:

db.query("SELECT * FROM table1 ;")
rows.each{|x| p x}
============================
maybe some refresh method to the Accessdb Class.?


Thanks a lot.
Rommel

Jurgen said...

Thanks for the info on how to start.

I have one suggestion.
I needed to know the data type for the fields as wel.
What I did was inserting the following code just after the field.Name statement(after the end):
@types = []
recordset.Fields.each do |field|
@types << field.Type
end

and I added the types to the top of the class with the other attributes.

dejan said...

From a previous post (2007), the user was able to create a new Catalog.

# SpaceRep.rb
require 'win32ole'
connection = WIN32OLE.new('ADODB.Connection')
'Data Source = is your Access file
connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=users.mdb')
cat = WIN32OLE.new("ADOX.Catalog")
cat.ActiveConnection = connection

I have tried the same code in ruby 1.8.6 but it fails. Its an Unrecognized Database format.

In addition I create a new file:
File.open(mdb_file, File::RDWR|File::CREAT)

and then attempt to connect to it and write a Catalog:

connection_string = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='
connection_string << @mdb

#create connection
@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)

#Create new database
@cat = WIN32OLE.new('ADOX.Catalog')
@cat.ActiveConnection = @connection

and it fails on the last line.

Raum Dellamorte said...

I'm having trouble finding a record by a DateTime value. Ruby stores DateTime as an integer and Access stores DateTime as a float.

When I find a record that has a DateTime in it, Ruby displays it like this:
2011-08-16 07:07:53 -0500

that same DateTime converted to an integer in ruby:
1313496473

And through trial and error I discovered that Access stores that same DateTime like this:
40771.2971412037

So the query "SELECT * FROM table WHERE start_dttm = 40771.2971412037" works but I am in serious need of being able to convert back and forth between that format and something human readable.

Even knowing what to call that format might help. Or maybe I'm just doing it altogether wrong!

Any help is appreciated :)

- Raum

Raum Dellamorte said...

I managed to help myself! My SQL statement should have been:

db.query("SELECT * FROM table WHERE start_dttm = #2011-08-16 07:07:53#")

Microsoft... Reinventing counter-intuitive.

-Raum

Anonymous said...

Hey great work guys,but i have a question that, getrows doesn't work with ruby 2.0 version ,need to use it for select query,getrows.transpose too not working,please help

David Mullet said...

@Anonymous:

What error are you getting?

The GetRows() and transpose() methods work for me, using:

Windows 8.1
Ruby 2.0.0p247

I'm running Office 2007 (not 2003 as when I wrote the article), so I did need to change the Provider in the connection string to:

Provider=Microsoft.ACE.OLEDB.12.0

Otherwise, the class works for me exactly as defined---under Ruby 2.0 MRI. I have not had success with it on JRuby.

David

Unknown said...

thank you for your valuable post.
I have a mac and an access db located on windows server PC which data is changing daily.
Both may mac and windows pc are in the same network and also I have permission to connect to server remotely by telnet.
How may I read data from my mac using ruby?

Volker said...

to prevent sql injections I ve tried the following code

sql = "INSERT INTO tblTest ([myName], [integerNumber], [isRed]) VALUES(?, ?, ?);"

ocmd = WIN32OLE.new('ADODB.Command')
ocmd.CommandType = 1
ocmd.ActiveConnection = @connection
ocmd.CommandText = sql

values.each_with_index do |value, index|
ocmd.Parameters.Item(index).Value = value
end

however if a value is nil execution fails... Does anybody know how I may pass a null value successfully using parameters ??

Martin Bach said...

Help!

I have some code (cribbed from the fine original example from this blog) to open an MsAccess database; thus:

connection_string = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
connection_string << @mdb
@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)

This has worked perfectly using Ruby 1.8.

I am now staring to use Ruby 2.3.0 (well - these things take time).

Unfortunately, when I run this code, I get the following error message:

Error (in OLE method 'Open':)
OLE error code 800A0E7a in ADODB.Connection
Provider cannot be found.It may not be properly installed

I cannot find any useful clues anywhere as to what has changed/needs to be amended. Can anyone help?

Thanks

David Mullet said...

@Martin:

Try replacing...

Provider=Microsoft.Jet.OLEDB.4.0

...with...

Provider=Microsoft.ACE.OLEDB.12.0

...and let me know if that resolves the issue.

-David

Martin Bach said...

David

Thanks for the suggestion, but precisely the same error as before.

Martin

Martin Bach said...

David

To be clear about matters, here is the abstracted test code, where I switch the comment between trying to open either the mdb or accdb file.

(Note: I did try previously your suggestion of trying to open the .mdb file with the ACE engine).

#===================================================================
require 'win32ole'

file1 = 'C:\Temp_Laptop\Test.mdb'
file2 = 'C:\Temp_Laptop\Test.accdb'

p 'not found file1' if !File.exist?(file1)
p 'not found file2' if !File.exist?(file2)

connection_string = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + file1
# connection_string = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' + file2

@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)

p ' opened'
#===================================================================

When run using Ruby 2.3 I get this for either run:


Error: (in OLE method `Open': )
OLE error code:800A0E7A in ADODB.Connection
Provider cannot be found. It may not be properly installed.
HRESULT error code:0x80020009
Exception occurred.

If I run using Ruby 1.8 I get the expected message 'opened' for either option.