General programming strategy

ruby-talk
1

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?

Any suggestion would be appreciated.

Yannick

2

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

3

"James Britt" <james_b@neurogami.com> wrote in message
news:43386378.5030009@neurogami.com...

Yannick Turgeon wrote:

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.)

Yes, you can do this with the WinHTTP library in either of these language
environments.

If this is the case, then you can write a Ruby app server that runs under
WEBrick...

As an alternative to this, you can attach a Ruby script to the running
Access application using WIN32OLE - I think that might be easier. That would
allow you to essentially add functionality to your application in Ruby
instead of VBA.

Cheers,
Dave

4

James,

Thanks to reply. I spent a couple hours yesterday to look at WEBrick,
Wee, Og. Main Nitro webpage was down and it seems I couldn't find good
information elsewhere. I'm really impressed with these. What an
abstraction all this is! I did knew Rails. Not very much though. But
enough to be aware of all the power "under".

It seems that I could use this in the former project. Wee and Webrick
are keeping state which is a big advantage in my situation. Rails
particularity of "convention over configuration" prevent me to use it
because current tables aren't respecting Rail's convention. I would
have try to partially and progressively use it as front-end. Maybe
Nitro would be good for me in that sens? Is it a MVC too? I'll take a
look when the site will be up again.

As an alternative to this, you can attach a Ruby script to the running
Access application using WIN32OLE - I think that might be easier. That would
allow you to essentially add functionality to your application in Ruby
instead of VBA.

Dave,

I'm not sure how you see things using WIN32OLE. Wouldn't be Access that
should embed Ruby instead of Ruby embedding Access? How would you apply
that to my situation?

Thanks all for your time.

Yannick

5

Yannick Turgeon wrote:

It seems that I could use this in the former project. Wee and Webrick
are keeping state which is a big advantage in my situation. Rails
particularity of "convention over configuration" prevent me to use it
because current tables aren't respecting Rail's convention.

You can use Rails with non-Rails-ish schemas:
http://wiki.rubyonrails.com/rails/pages/HowToUseLegacySchemas

Maybe Nitro would be good for me in that sens? Is it a MVC too?

Yes.

As an alternative to this, you can attach a Ruby script to the running
Access application using WIN32OLE - I think that might be easier. That
would
allow you to essentially add functionality to your application in Ruby
instead of VBA.

Dave,

I'm not sure how you see things using WIN32OLE. Wouldn't be Access that
should embed Ruby instead of Ruby embedding Access? How would you apply
that to my situation?

Embedding Ruby... you can certainly run external Ruby scripts from within
your VBA code and use the results, but the built-in Shell command is pretty
weak. If you want to do it that way, you're best off importing some of the
Windows API. Details of that are off-topic here, but this is a rather
limited approach in any case.

What I'm talking about, though, is easy: Access offers a COM API so that you
can easily script an Access application from an external VBScript or other
COM interface. Ruby's is WIN32OLE. A look at the following page will give
you the idea. (It's Excel, not Access, but the same principle applies - you
can easily do anything you can do in VBA from your Ruby script)

Cheers,
Dave