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