Skip to content

Instantly share code, notes, and snippets.

@ecleel

ecleel/mdb.rb

Forked from dtolj/gist:784634
Created February 8, 2012 05:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ecleel/1765587 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ecleel/1765587 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Abdulaziz Al-Shetwi renamed this gist Feb 8, 2012. 1 changed file with 26 additions and 15 deletions.
    41 changes: 26 additions & 15 deletions gistfile1.rb → mdb.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -2,10 +2,10 @@
    require 'win32ole'
    require 'csv'

    mdb_file="c:/dtolj/ruby_projects/phone.accdb"
    mdb_file="c:/Sites/labs/50q.bok"

    class AccessDb
    attr_accessor :mdb, :connection, :data, :fields
    attr_accessor :mdb, :connection, :data, :fields, :catalog

    def initialize(mdb=nil)
    @mdb = mdb
    @@ -15,26 +15,24 @@ def initialize(mdb=nil)
    end

    def open
    #connection_string = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
    connection_string = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
    #Access 2010 connection string
    connection_string = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source='
    # 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

    #catalog.create(connection_string)
    #Source="#{@mdb_file}"
    @connection = WIN32OLE.new('ADODB.Connection')
    @connection.Open(connection_string)

    @catalog = WIN32OLE.new("ADOX.Catalog")
    @catalog.ActiveConnection = @connection

    # catalog.create(connection_string)
    # Source="#{@mdb_file}"
    end

    def query(sql)
    recordset = WIN32OLE.new('ADODB.Recordset')
    puts recordset.ole_methods
    recordset.Open(sql, @connection)
    @fields = []
    recordset.Fields.each do |field|
    @@ -48,6 +46,12 @@ def query(sql)
    recordset.Close
    end

    def tables
    tables = []
    @catalog.tables.each {|t| tables << t.name if t.type == "TABLE" }
    tables
    end

    def execute(sql)
    @connection.Execute(sql)
    end
    @@ -62,5 +66,12 @@ def close

    db = AccessDb.new(mdb_file)
    db.open
    # db.query("Select * from b1611")
    # puts db.connection.invoke "OpenSchema" , "b1611"
    puts db.tables
    # puts db.fields
    # puts db.data

    # puts db.execute "select * from tables"

    # db.close
  2. Abdulaziz Al-Shetwi renamed this gist Feb 8, 2012. 1 changed file with 0 additions and 5 deletions.
    5 changes: 0 additions & 5 deletions gistfile1.txt → gistfile1.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,3 @@
    #
    #
    #
    #
    #
    require 'rubygems'
    require 'win32ole'
    require 'csv'
  3. @dtolj dtolj revised this gist Jan 18, 2011. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,8 @@
    #
    #
    #
    #
    #
    require 'rubygems'
    require 'win32ole'
    require 'csv'
  4. @dtolj dtolj created this gist Jan 18, 2011.
    66 changes: 66 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,66 @@
    require 'rubygems'
    require 'win32ole'
    require 'csv'

    mdb_file="c:/dtolj/ruby_projects/phone.accdb"

    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='
    #Access 2010 connection string
    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

    #catalog.create(connection_string)
    #Source="#{@mdb_file}"
    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

    #create empty ms access file
    file = File.open(mdb_file, File::RDWR|File::CREAT)

    db = AccessDb.new(mdb_file)
    db.open