[Zope] MS Access interfacing

[Joel Burton]

On Fri, 20 Jul 2001, Casey Duncan wrote:

> > There's no problem getting data from an Access database via ODBC as long as
> > the ODBC driver and the database are on the **same Windows machine**.  I've
> > not been able to make this work when the server+odbc is on a different
> > machine from the database.
> 
> That is because Access is not a database server, it is a client-based
> database program.
> > 
> > My inclination would be to put a Zope installation on the  Windows machine
> > that has the Access database (you could copy the database every once in a
> > while if it doesn't get updated too often.  That way you could avoid putting
> > anything on someone else's machine).  Build Zope pages that return the data
> > as lists, I would think, and get them into your Zope Linux installation that
> > way.
> 
> You could get around this problem by using linked tables in Access. You
> can physically store the Access mdb file with the data in it anywhere so
> long as you can access that file over the network. Then setup Access on
> the Zope machine and create a database that just contains tables that
> are linked to the remote mdb file. Then you get completely live data all
> the time.
> > 
> > Of course, to actually get the data from Zope you have to write queries
> > against the database, but that's separate from getting this transfer
> > machinery going.
> 
> The linked tables will take care of this. From Zope's point of view it
> won't matter.
> > 
> > Another approach would be to get a python xml-rpc server running on the
> > Windows machine.  That would use mxodbc to get data from the database and
> > return it to Zope using xml-rpc.  This should be pretty easy to do.
> 
> Sounds like a lot of overhead.

Yeah, but with some fairness, setting up a database server and linking the
tables from Access can be tricky, as the "average user" can usually no
longer make any structural changes (even minor ones) to the tables except
through semi-arcane SQL commands or specialized programs specific to that
database server. [ MS SQLServer notwithstanding, but of course, then
you're into serious $$$]

We use linking to PostgreSQL backend at our org very successfully, but it
does limit the ability of my semi-power-users to make structural changes
(even minor ones) to our tables. Of course, that might be a *good*
thing... ;-)

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