Re: Seeking help on making an Access-ODBC-SSH-PostgreSQL connection

In the tunneling dialog in putty, the host should be dhat.org, not
highspeedrails.

_____

From: Ken Winter [mailto:ken(at)sunward(dot)org]
Sent: Sunday, December 24, 2006 4:36 PM
To: pgsql-odbc(at)postgresql(dot)org
Cc: 'Jim Myers'; Michael Adams - DHAT; 'Ken Winter'
Subject: Seeking help on making an Access-ODBC-SSH-PostgreSQL connection

Hello PgSQL-ODBC ~

I'm trying to use MS Access (on my Windows XP client) as a front-end query
and reporting tool for a PostgreSQL database, which is running on a
commercial web host server (http://www.highspeedrails.com
<http://www.highspeedrails.com/> ).

My objective is to get the PostgreSQL database tables to show up in Access
as linked tables, on which I can then build queries and reports. I figured
that to do this, I need to:

1. Install the psqlODBC driver. I've done that.
2. Set up a DSN for this driver and the target database. I've done
that.
3. Somehow get an SSH tunnel open, with port forwarding. This part of
the story gets long. I have connected to highspeedrails via SSH using tools
that have built-in SSH functionality (PGLA, EMS SQL Manager) and specific
SSH tools (SSH Secure Shell, PuTTY). I have failed with another tool with
built-in SSH (Navicat). I have tried to use PuTTY and SSH Secure Shell to
make the tunnel available to other tools (namely Access) that apparently
aren't capable of digging their own tunnels.
4. With the tunnel (hopefully) open, fire up Access and try to link a
table from the PostgreSQL database.

So, here's a true story (see screen prints in attached files):

1. Start a session using PuTTY (see screen prints "PuTTY Session
01.JPG", "PuTTY SSH 01.JPG", and "PuTTY Login 01.JPG").

2. In an empty Access database, select Insert / Table / Linked Table /
ODBC Databases, and select the psqlODBC driver DSN (see screen prints "ODBC
Driver Setup 01.JPG" and "Access Data Source 01.JPG).

3. Result: the error message shown in "Access ODBC call failed error
01.JPG".

I don't have enough experience with all these technologies to analyze what
went wrong in this and many other scenarios I have tried (details of the
others on request).

Can anyone help - with specific advice or a useful reference?

~ TIA
~ Ken