Any tips on getting Rails to run with an Access back-end?

Question

I shudder to ask, but my client might offer no other SQL (or SQL-like) solution. I know Access has some SQL hooks; are they enough for basic ActiveRecord?

Later:

I appreciate all the suggestions to use other databases, but trust me: I've tried convincing them. There is an "approved" list, and no SQL databases are on it. Getting something onto the list could take more than a year, and this project will be done in three weeks.

Answer 1

Is there any possibility of using SQLite? It's not the most efficient database system out there, but it works well enough for most intents and purposes. It might be more feasible to convince the client to get SQLite installed than a full MySQL or Postgres setup. Rails, of course, has no qualms using SQLite databases.

Answer 2

It's a long shot but there's an ODBC adapter for ActiveRecord that might work.

Answer 3

Another option that is more complicated but could work if you were forced to do it, is to write a layer of RESTful web services that will expose Access to rails. If you are careful in your design, those RESTful web services can be consumed directly by ActiveResoure which will give you a lot of the functionality of ActiveRecord.

Answer 4

There are some wierd things in Access that might cause issues and I don't know if ODBC takes care of it. If it does @John Topley is right, ODBC would be your only cance.

1. True in access = -1 not 1
2. Access treats dates differently than regular TSQL.
3. You might run into trouble creating relations.

If you go with access, will probably learn more about debuging AcriveRecord then you ever cared to ( which might not be a bad thing)

Answer 5

It's been a while since I've used Access, but using it as a back-end is just about the last thing I would do.

For a beginner, Access is a pretty good front end to a database. But for storing the data itself, it can't handle more than a couple of users at the same time.

Given the excellent free/open source alternatives, there is no reason to use Access to store data. MySQL, PostgreSQL and SQLite should cover pretty much all database use cases.

Answer 6

There seems to be something of an Access connection adapter here: http://svn.behindlogic.com/public/rails/activerecord/lib/active_record/connection_adapters/msaccess_adapter.rb

The database.yml file would look like this:

development:
  adapter: msaccess
  database: C:\path\to\access_file.mdb

I'll post more after I've tried it out with Rails 2.1

Answer 7

You should really talk them into allowing SQLite. It is super-simple to setup, and operates like Access would (as a file sitting next to the app on the same server).

Answer 8

Firstly, you really want to be using sqlite.

In my experience Access itself is a pile of [redacted], but the Jet database engine it uses is actually pretty fast and can handle some pretty complex SQL queries. If you can find a rails adapter that actually works I'd say you'll be fine. Just don't open the DB with the access frontend while your rails app is running :-)

If your client is anal enough to only allow you to develop with an approved list of databases, they may be more concerned by the fact that Jet is deprectated and will get no more support from MS.

This might give you some ammunition in your quest to use a real database. Good luck

Answer 9

I appreciate all the suggestions to use other databases, but trust me: I've tried convincing them. There is an "approved" list, and no SQL databases are on it. Getting something onto the list could take more than a year, and this project will be done in three weeks.

I can't be the only person who finds a process that permits Rails but prohibits SQL to be totally hilarious, right? :)

Answer 10

Have you considered migrating from Rails to VB6?

Answer 11

Maudite wrote:

True in access = -1 not 1

Not correct. True is defined as not being false. So, if you want to use True in a WHERE clause, use Not False instead. This will provide complete cross-platform compatibility with all SQL engines.

All that said, it's hardly an issue, since whatever driver you're using to connect to your back end will properly translate True in WHERE clauses to the appropriate value. The only exception might be in passthrough queries, but in that case, you should be writing the SQL outside Access and testing it against your back end and just pasting the working SQL into the SQL view of your passthrough query in Access.

Maudite wrote:

Access treats dates differently than regular TSQL.

Again, this is only going to be an issue if you don't go through the ODBC or OLEDB drivers, which will take care of translating Jet SQL into TSQL for you.

Maudite wrote:

You might run into trouble creating relations.

I'm not sure why you'd want an Access application to be altering the schema of your back end, so this seems to me like a non-issue.