Activerecord adapter for msaccess mdb files

Rails
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
http://www.ch-werner.de/rubyodbc/
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 H. 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

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!