activerecord adapter for msaccess mdb files

rubyonrails-talk
1

Good afternoon,

I've had a hard time today trying to connect a rails app (still new to it, overall, though I do have some sintra experience) to an existing access mdb.

I've installed the ODBC binding for ruby from ODBC Binding for Ruby I've also seen/used: http://odbc-rails.rubyforge.org/ I've tried the one-click installer for both ruby 1.8 and 1.9.x. I've been able to connect via ODBC and IRB straight ruby to get a simple select query going.

irb(main):001:0> require 'DBI' => true irb(main):002:0> conn = DBI.connect('DBI:ODBC:mdbtest') c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.5/lib/dbi.rb:93: warning: already initial ized constant VERSION c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.5/lib/dbi.rb:96: warning: already initial ized constant API_VERSION c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.5/lib/dbi.rb:100: warning: already initia lized constant DEFAULT_TRACE_MODE c:/ruby/lib/ruby/gems/1.8/gems/dbi-0.4.5/lib/dbi.rb:101: warning: already initia lized constant DEFAULT_TRACE_OUTPUT => #<DBI::DatabaseHandle:0x3335d60 @handle=#<DBI::DBD::ODBC::Database: 0x3335c88 @handle=#<ODBC::Database:0x3335cb8>, @attr={}>, @driver_name="odbc", @convert_ty pes=true, @trace_output=nil, @trace_mode=nil> irb(main):003:0> conn.tables => [nil, nil, nil, nil, nil, nil, nil] irb(main):004:0> conn.select_all('select * from employees') => [[1, "thomas", 31], [2, "bob", 42]] irb(main):006:0> quit

But I'm stumped in getting it to work with activerecord. If I have a new rails app, and my database.yml looks like this:

development:   adapter: msaccess   mode: odbc   dsn: mdbtest

When I run a rake:db:migrate I get this:

(in C:/sandbox/skunk/mdb) rake aborted! Please install the msaccess adapter: `gem install activerecord- msaccess-adapter` (no such file to load -- active_record/connection_adapters/ msaccess_adapter)

(See full trace by running task with --trace)

I've even tried the 3+ year old version of the activerecord-msaccess- adapter and put it into the vendor/... location stated. No go.

Current setup: Windows XP. Ruby: ruby 1.8.7 (2010-08-16 patchlevel 302) [i386-mingw32] Rails: rails (2.3.4) gem-v: 1.3.7

Thoughts?

What are people currently doing today?

Thank you for your time!

-Tom

2

Adapters are actual ActiveRecord adapters. There’s no msaccess adapter available, although there is a SqlServer adapter.

If you can get the connection going through ODBC, you could probably write your own msaccess adapter - they’re not that hard to write and you could use the sqlite3 adapter as a template.

I’ve never successfully gotten a Rails app to talk to MS Access and I’ve been at this a long long time. My recommendation is to convert the access db to mysql or sqlserver and go from there. (We used to do ASP pages with MS Access - let me just say that web apps + ms access == recipe for disaster.)

3

+++ We had to script the server to restart every day or it would ball up and sit in the corner sucking its thumb.

Walter

4

Brian Hogan wrote in post #960756:

Adapters are actual ActiveRecord adapters. There's no msaccess adapter available, although there is a SqlServer adapter.

If you can get the connection going through ODBC, you could probably write your own msaccess adapter - they're not that hard to write and you could use the sqlite3 adapter as a template.

Why not just use the ODBC adapter?

I've never successfully gotten a Rails app to talk to MS Access and I've been at this a long long time. My recommendation is to convert the access db to mysql or sqlserver and go from there.

I'd actually recommend neither of those, but would suggest PostgreSQL instead.

(We used to do ASP pages with MS Access - let me just say that web apps + ms access == recipe for disaster.)

Really? To be sure, Access is a pretty bad DB, but I remember doing production Web applications with ColdFusion and ODBC/Access way back in 1999, when no one knew any better. I don't recall major DB issues, though it's possible that the sysadmin didn't mention them.

Best,

5

Cool ideas, guys. Thank you. Right now this is for a wireframe that an IT director built the schema for in the Access DB. I'm just going to toss it, copy the schema over to sqlite3 and wireframe with scaffolds in Rails 3 + the latest version of all the other stuff. It's that whole "time to get out of dependency hell" vs "time to get it converted to a better method" argument.

Thanks again!