Yannick Turgeon wrote:
> Hello all,
> 
> I'm neighter a newbie nor an experimented rubyist and I've got a
> "general" question. At work, we use a relatively big MS-Access
> front-end application (with MS-SQL Server as back-end). It is really a
> pain to debug this application and to add functionnaly because it is
> poorly design... and the language (VBA) is quite limited and
> proportionnaly frustrating.
> 
> What I'd like, it's to change things and use Ruby to do some work. A
> kind of programming interface between MS-Access and SQL-Server to
> remove complex parts for VBA and use Ruby instead. I cannot rebuild
> this big application from scratch (200+ forms, 200+ queries, 200+
> tables, 100+ reports) so I have to live with some important constaints.
> 
> 
> Can I use ruby to do a part of the job, a kind of dll or, even better,
> a way that would keep state between different call?
> 

Yes, I'm sure there are a couple of ways to do this.

> Any suggestion would be appreciated.

Here's one:

I believe VBA can make HTTP calls using Microsoft's XmlHttpRequest 
object.  (I can't actually test this right now, because my machine has 
no VBA-capable applications that I'm aware of.  Not sure if plain old 
VBScript behaves the same; VBA seems to float between VBScript and 
actual Visual Basic.)

If this is the case, then you can write a Ruby app server that runs 
under WEBrick.  Pass data around using GET and POST, to and from the 
client and the Ruby server, and have the Ruby server delegate database 
calls out the back to the SQL Server.  (BTW, see the Mousehole app for a 
good example of packaging up a WEBrick app as a Windows EXE.)

The Ruby server could be hand-rolled, or built using Og/Nitro, or Wee, 
or Rails; you have choices.

The Access app then becomes something of a chubby client; pretty sure it 
understand XML RecordSets (or something along those lines).

Anyway, something to think about.

James

-- 

http://www.ruby-doc.org - The Ruby Documentation Site
http://www.rubyxml.com  - News, Articles, and Listings for Ruby & XML
http://www.rubystuff.com - The Ruby Store for Ruby Stuff
http://www.jamesbritt.com  - Playing with Better Toys