Access & Ruby on Rails: Software + Services

Published 25 October 07 08:30 AM

Prior to joining the team here this summer I was a partner in a couple app development shops in Toronto (M7 Database & Unspace) that specialized in a software + services approach to development. We blended Access with Ruby on Rails to create better experiences for both our customers and their customers through the web. Many people I have met think the web is the be all end all and that rich client software is going away. I disagree; the web does a lot of things well like democratize content and allow your app to reach across the world but rich clients often provide a deeper user experience and give users richer feature sets and tools to control and adjust their systems. I wanted to show off an example of Access in this context.

Factor

As a private non-profit organization, FACTOR is dedicated to providing assistance toward the growth and development of the Canadian independent recording industry.

They have been a long term client of my last company (10+ years) and our Access/SQL based system has been running all aspects of their business from application processing through to government reporting and integration to their financial systems. As the web became more of a focus point they had a new set of opportunities at hand.

New goals:

  • Allow applicants to apply online and submit their material digitally. This would cut down on a huge administrative burden of manually processing hard-copy applications.
  • Allow juries to operate online. This would cut the large cost of printing multiple copies of the applications and shipping them around the country (Canada is big) for the various levels of juries. It would also allow remote jurors to participate in the process.

So why didn’t we just migrate the whole system over to the web? Because their old goals hadn’t changed.

  • Staff needed to create and manage custom ad-hoc reports as well as create new mail merge docs and integrate them into the system.
  • Factor wanted the control to be able to design and create new forms and tables without having to call in developers.
  • They didn’t want to mix the web based data with the sensitive applicant info on their local database.
  • They needed to keep the rich integration with their local network financial system.

A software + services approach allowed FACTOR to have a cutting-edge rich user experience on the web and leverage the rich client to do what it did best allowing their staff to have full control over the application without developer support.

System details

  • Access Front End with MSSQL Express backend
  • ACCPAC integration via ODBC
  • Linked ODBC MYSQL tables for website data
  • Ruby on Rails web application

You can see the FACTOR application system at onlineapp.factor.ca and find out more about FACTOR at factor.ca

We would love to hear about other solutions to web integration from the Access community! Also I will post some more examples in the coming weeks.

by Ryan McMinn

Comments

# D. Bruce Moore said on October 25, 2007 6:16 PM:

I would love to hear about the challenges of merging Ruby and Access!  Any chance of getting details?

# Russell Sinclair said on October 25, 2007 6:22 PM:

One thing that we at M7 have added to the mix since Ryan left is using a Ruby XML feed to drive data into Access. The online application used some interesting methods to get around dynamic field population and application versioning. The Unspace group provided us with an XML feed that simplified the data structure for us so that we could simply load XML datasets using ADO and bring the data into access tables. Where we used queries on linked tables before, we have now moved to loading data using XML feeds.

# AlexDybenko said on October 26, 2007 4:06 AM:

Hi,

doing the same approach, but using ASP.NET and Telerik controls. Of course, not so easy as in Access, but it looks and functions almost the same way, customers like this, can easy switch from web to access and back

# Garry Robinson said on October 26, 2007 6:39 AM:

Great that Ryan jumped to the Access team, his experiences with the Web and Access will be terrific. When I was in Seattle, Ryan inspired me to get into MSXML so that I could talk to basecamphq.  I am building a really useful invoicing system called ThickToast that works with Basecamp.  See picture www.thicktoast.com

# Zac Woodall said on October 27, 2007 11:30 PM:

What Gary said.  It is great to have Ryan on board.  Attitudes are changing around here... traditional software isn't, well, traditional anymore.  People like Ryan are beginning to crop up here and there, and I think us oldschoolers are more receptive to their ideas now than we have been at any point in the past.

For those who are looking, Ryan's influence will be clearly apparent in Access 14.  

# Mark Jones said on October 30, 2007 8:11 AM:

I'd like to hear more about the Ruby on Rails integration. We have a web interface for our Access app (ASP and ASP.NET), but because our back end is SQL Server it communicates directly with the data on the server, so it doesn't actually 'integrate' with the Access app at all.

# Steve Goldring said on October 30, 2007 9:24 AM:

If I understand Russell correctly, the Access front end no longer maintains a direct connection to the back end. ADO (in Access VBA?) is used to consume XML fed from the web. Apparently the Access application does not need to share any data with the web - it merely consumes what the web feeds it.

If this is correct, there's nothing too special here. I look forward "in the coming weeks" to other examples. I'm interested to hear about using Access as a front end to a SQL Server database (for example) on a website - where both the website and the Access database work with the SQL data. Or is this, architecturally, simply a bad idea?

# Ryan McMinn said on October 31, 2007 6:20 PM:

Hey Steve, The factor site also does what you mention using Access as a front end for a web hosted MYSQL server (works just as well with MSSQL). The latest post about releaseme.ca is the same model where both the Rails app and Access talk to the same web hosted database.

New Comments to this post are disabled
Page view tracker