ruby and accdb (ms access)

Question

If I have a base windows xp system, ruby and an ms access 2007 file (say c:/foo/bar.accdb) file, what's the least intrusive method for reading that .accdb file.

• What needs to be installed on the xp system.
• What is the specific connection string.

Answer 1

Something along these lines should get you started. Of course, you'll need to modify some of the values like; path, filename, SQLstatement, etc.

MDB file (Access 2003 format and older) using the Jet engine

require 'win32ole'
connection = WIN32OLE.new('ADODB.Connection')
connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;
                 Data Source=c:pathfilename.mdb')

ACCDB file (Access 2007 format and newer) using the ACE engine

require 'win32ole'
connection = WIN32OLE.new('ADODB.Connection')
connection.Open('Provider=Microsoft.ACE.OLEDB.12.0;
                 Data Source=c:pathfilename.accdb')

To execute a SQL query that doesn't return data use:

connection.Execute("INSERT INTO Table VALUES ('Data1', 'Data2');")

To perform a query that returns a recordset:

recordset = WIN32OLE.new('ADODB.Recordset')
recordset.Open(SQLstatement, connection)