How do I query a MS Access database table, and exp

Question

I'm new to Ruby, and I'm trying to query an existing MS Access database for information for a report. I want this information stored in an Excel file. How would I do this?

Answer 1

Try one of these:

OLE:

require 'win32ole'

class AccessDbExample
  @ado_db = nil

  # Setup the DB connections
  def initialize filename
    @ado_db             = WIN32OLE.new('ADODB.Connection')
    @ado_db['Provider'] = "Microsoft.Jet.OLEDB.4.0"
    @ado_db.Open(filename)
  rescue Exception => e
    puts "ADO failed to connect"
    puts e
  end

  def table_to_csv table
    sql = "SELECT * FROM #{table};"

    results = WIN32OLE.new('ADODB.Recordset')
    results.Open(sql, @ado_db)

    File.open("#{table}.csv", 'w') do |file|
      fields = []
      results.Fields.each{|f| fields << f.Name}
      file.puts fields.join(',')

      results.GetRows.transpose.each do |row|
        file.puts row.join(',')
      end
    end unless results.EOF

    self
  end

  def cleanup
    @ado_db.Close unless @ado_db.nil?
  end
end

AccessDbExample.new('test.mdb').table_to_csv('colors').cleanup

ODBC:

  require 'odbc'

    include ODBC

    class AccessDbExample
      @obdc_db = nil

      # Setup the DB connections
      def initialize filename
        drv                 = Driver.new
        drv.name            = 'AccessOdbcDriver'
        drv.attrs['driver'] = 'Microsoft Access Driver (*.mdb)'
        drv.attrs['dbq']    = filename
        @odbc_db            = Database.new.drvconnect(drv) 
      rescue 
          puts "ODBC failed to connect"
      end

      def table_to_csv table
        sql = "SELECT * FROM #{table};"

        result = @odbc_db.run(sql)
        return nil if result == -1

        File.open("#{table}.csv", 'w') do |file|
          header_row = result.columns(true).map{|c| c.name}.join(',')
          file.puts header_row

          result.fetch_all.each do |row|
            file.puts row.join(',')
          end
        end

        self
      end

      def cleanup
        @odbc_db.disconnect unless @odbc_db.nil?
      end
    end

    AccessDbExample.new('test.mdb').table_to_csv('colors').cleanup

Answer 2

Why do you want to do this? You can simply query your db from Excel directly. Check out this tutorial.

Answer 3

As Johannes said, you can query the database from Excel.

If, however, you would prefer to work with Ruby...

You can find info on querying Access/Jet databases with Ruby here.

Lots of info on automating Excel with Ruby can be found here.

David