How to connect to an MS Access database using Ruby and ODBC

If you search for an answer to this question in Google, you will get mostly sarcastic answers and if you're lucky maybe some help.

You must first download Ruby ODBC from Christian Werner's site and copy odbc.so and odbc_utf8.so into the ...\ruby\1.8\i386-msvcrt directory where you installed Ruby. Uf you have installed InstantRails in default location, it should be under C:\InstantRails\ruby\lib\ruby\1.8\i386-mswin32

In the example below I connect to an Access file (c:\ruby.mdb) and print the contents of a table (Table1)

require 'DBI'

access_dsn_base = 'DBI:ODBC:Driver=Microsoft Access Driver (*.mdb);DBQ='
access_file = 'C:\ruby.mdb'

DBI.connect(access_dsn_base+access_file) do |dbh|
  dbh.select_all('Select * From Table1') {|row| p row}
end

Similarly, you can use DBI:ODBC to read MS Excel files.
In the example below I connect to an Excel file (c:\my_file.xls) and print the contents of a spreadsheet (Sheet1)

require 'DBI'

excel_dsn_base = 'DBI:ODBC:Driver={Microsoft Excel Driver (*.xls)};DBQ='
excel_file = 'C:\my_file.xls'

DBI.connect(excel_dsn_base+excel_file) do |dbh|
  dbh.select_all('Select * From [Sheet1$]') {|row| p row}
end

My configuration is Windows XP + MS Access 2000 + MS Excel 2002 + Ruby 1.8.6 + Ruby ODBC 0.9995