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

["J. Michael Adams" <jmadams(at)dhat(dot)org>]

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). 

 

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