[Zope] MS Access interfacing

[Joel Burton]

On Fri, 20 Jul 2001, Bruce wrote:

> My best success so far has been with Excel spreadsheets. In this 
> case, the page maintainer stores it on an NT server, and I run a cgi 
> script that ftp's a copy over, and then it gets displayed via 
> xl2html. This has the advantage of making a local copy for a backup, 
> in case something untoward should happen, and the it's completely 
> transparent to the person responsible for maintaining the page. As 
> far as he's concerned, it just magically gets updated on the website.
>
> [...]
>
> Now I'm faced with searching a 20Mb Access database and displaying 
> selected records from it.
> 
> Downloading a new copy with every access at 20Mb is prohibitive, and 
> it's not updated so often as to be important. I can manually stash it 
> wherever necessary as needed.
> 
> The first question is where to stash it. I want to avoid installing 
> new software or services on the various machines, though I certainly 
> could, within reason and limitiation. Ideally, I want to just stash 
> it on the Linux server, and have Zope take care of the interface and 
> display. But looking at the Jet ODBC connectors, it appears that they 
> really want to live in Windows.
> 
> Which is all well and good. The database maintainer is using a 
> Windows machine, and I have our local NT server, either will do, but 
> I'd rather not install Python on either of them if I didn't have to, 
> though I can if needs must.
> 
> Which also brings me the question, how does my Linux Zope 
> installation talk to the Windows server hosting the database? By way 
> of the connector?

If I understand, you want Zope to be able to dig into the Access
database and show dynamic (or semi-dynamic; you say that it could be
mannually updated) information.

This is *very* different from an Excel file; you can convert an XL file to
HTML, but you can't "convert" an Access DB to HTML -- you *can* make an
HTML page for each of the tables, but usually people want one page for
each record, showing the information in a customized way.

Zope excels at this -- you can use the ZSQL Methods to dig into your
database. The challenge is digging into an Access .mdb file from
Linux.

There's no (free) way (that I know of) to access an Access .mdb file from
Linux -- MS hasn't written a driver, and aren't likely to do so. There is
a project a SourceForge, MDBTools, that reverse-engineers that .mdb
format, but it's not useful yet.

There are lots of different strategies.

You could:

1) regularly clone the db to linux

   * install PostgreSQL (very powerful), MySQL (very small),
     or Interbase (very easy) on your Linux box.

   * write a small python script that uses both an ODBC and PostgreSQL/
     MySQL/Interbase ODBC connector. Have it copy the data from the
     Access tables (via ODBC) to the Linux-DB tables.
     (or write it in Perl or, if you're not handy w/programming, do
     the conversion by hand or via one of the conversion programs
     available for the Linux DB.)

   * tap into this database from Zope. You'll have to install a
     database adaptor in Zope, but that's usually easy.

   Disadvantages: data isn't live; it's behind by several hours or
    a day, depending on how often the conversion script runs.

2) use a Linux DB instead

   * install a Linux DB.

   * Convert your data to the Linux DB, as above.

   * Install the Windows ODBC driver for your Linux DB on your
     Windows machines.

   * Set up your Access database to, instead of using local tables,
     use linked tables that get their data from the Linux server.

   Disadvantages: w/ linked tables, your end users can't easily
    modify their table structures (add field, etc.); sometimes,
    there are changes req'd to the Access DB.

   [ nb: I'm the author of the FAQ on how to do this w/PostgreSQL.
     See www.scw.org/pgaccess for the full story. ]

3) let Windows do the lifiting

   * install Zope on a Windows box.

   * Write the stuff for Zope, using ZODBC or ZJet Adaptor, to
     get the stuff from your Access DB.

   * Use redirects or ProxyPass from the Linux server to redirect just
     the relevant requests to the Winows Zope server; or, serve all
     Zope pages from Windows.

   Disadvantages: Windows 95/98 isn't exactly stable; your pages
    will show a different domain name.

4) hack together some flaky, non-DB way

   * write a macro/VBA code in Access to export table info to a
     text file (*very* easy to do in a macro).

   * write a python method to knock this text export into HTML snippets,
     and import these into Zope.

   Disadvantages: not very portable in the future.

Hope something here unlocks some ideas of where to go.

Good luck,

-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington