Re: [TCLUG:19135] Access to PostgreSQL via ODBC?

[Timothy Wilson <wilson@visi.com>]

On Fri, 23 Jun 2000, Troy A. Johnson wrote:

> You can export the Access tables to flat files, transmogrify them with
> Perl, and then use Perl to load PostgreSQL from the transmogrified flat
> files. I mention the transmogrification process because Access's text
> output will probably need to be modified, and you might want to use
> another more comfortable tool to load your database (though I don't know
> what that would be). I am assuming that the data will live in the
> PostgreSQL/MySQL database and you won't keep going back to the Access
> file. Is this coreect?

Why don't I fill everyone in a little bit on what I'm trying to do. Someone
might have an idea about a better overall approach.

All of the student data that our school district uses on a daily basis
(grades, schedules, attendance, personal information) lives in an Oracle
database at TIES, our district's ISP and creator of the TIES Student
Information System (TSIS). We contract with them to provide technology
services such as report card printing, scheduling, attendance, etc.

I'm trying to create a Web site that uses Zope. We'd like to make some of
that student data available to staff on our intranet and use other parts to
allow students to view their own attendance, grades, and eventually display
lists of upcoming assignments and other goodies.

The easiest solution would be to use Zope's Oracle Database adaptor and
connect to the TIES DB directly. They apparently don't like that idea. They
will, however, make data available to use in the form of tables for MS
Access--probably using a nightly mirror or something. This has been utilized
for various small projects before, although I haven't been involved with it.

The next easiest solution would be to connect directly to Access using ODBC.
Unfortunately the ODBC Database adaptor only works on Windows. There is no
Linux-compatible version. There is, however, a very nice PostgreSQL Database
adaptor which brings me to the third possibility...

I could install PostgreSQL on the Web server and do a nightly mirror of the
Access tables to Postgres. The problem is that I don't know how to do this
step.

We've also considered setting up our own Oracle DB here at school. No knows
anything about Oracle, however, and I don't think I have time to be
full-time teacher and part-time sys admin/Webmaster/ *and* Oracle DBA.

I'd love to hear any ideas that anyone might have.

-Tim

--
Tim Wilson      | Visit Sibley online:         | Check out:
Henry Sibley HS | http://www.isd197.k12.mn.us/ | http://www.zope.org/
W. St. Paul, MN |                              | http://slashdot.org/
wilson@visi.com |   <dtml-var pithy_quote>     | http://linux.com/