Re: versions of oDBC driver

Did you changed the Postgresql server and the client ODBC driver at the same time?
My guess is that the pg_hba is not configured to let you in,...

I would make sure the PostgreSQL server is set to having connection logging enabled, and see what type of
errors it is throwing on the server.

Secondarily, I find that not every parameter is essential for the connection string. You might trying
dropping your strConnParams, and if that helps, debugging them one at a time, or by halves.



Zlatko Matić wrote:
> Hello.
> The error is error 3151: "ODBC--connection to '{PostgreSQL}Localhost' failed".
> I have a form with text boxes for entering Database name, IP adress, username, password etc. When a user push the
confirmationbutton, a function "ConnectionToServer" is executed to: a) create connection string, b) to check whether
connectionstring works, c) to call functions for relinking linked tables and adjusting connection string in
pass-throughqueries. The code is following: 
>
> Option Compare Database
> Public strConnection As String
>
> Function ConnectionToServer(SERVER As String, PORT As String, SOCKET As String, DATABASE As String, USERNAME As
String,PASSWORD As String, ENCODING As String) As Boolean 
>
> Dim db As Object
> Dim qdf As Object
> Dim qdfSQL As String
> Dim rs As Object
>
> Dim strConnInfo As String
> Dim strConnUserPass As String
> Dim strConnParms As String
> Dim CurrentUser As String
> Dim A6 As String
>
> On Error GoTo ErrorHandler
>
> DoCmd.Hourglass True
>
>     Set db = CurrentDb
>
> '   PG_ODBC_PARAMETER           ACCESS_PARAMETER
> '   *********************************************
> '   READONLY                    A0
> '   PROTOCOL                    A1
> '   FAKEOIDINDEX                A2  'A2 must be 0 unless A3=1
> '   SHOWOIDCOLUMN               A3
> '   ROWVERSIONING               A4
> '   SHOWSYSTEMTABLES            A5
> '   CONNSETTINGS                A6
> '   FETCH                       A7
> '   SOCKET                      A8
> '   UNKNOWNSIZES                A9  ' range [0-2]
> '   MAXVARCHARSIZE              B0
> '   MAXLONGVARCHARSIZE          B1
> '   DEBUG                       B2
> '   COMMLOG                     B3
> '   OPTIMIZER                   B4  ' note that 1 = _cancel_ generic optimizer...
> '   KSQO                        B5
> '   USEDECLAREFETCH             B6
> '   TEXTASLONGVARCHAR           B7
> '   UNKNOWNSASLONGVARCHAR       B8
> '   BOOLSASCHAR                 B9
> '   PARSE                       C0
> '   CANCELASFREESTMT            C1
> '   EXTRASYSTABLEPREFIXES       C2
>
> Select Case ENCODING
> Case "DEFAULT"
>     A6 = ""
> Case "UNICODE"
>     A6 = "CLIENT_ENCODING=UNICODE"
> Case "SQL_ASCII"
>     A6 = "CLIENT_ENCODING=SQL_ASCII"
> Case "WIN1250"
>     A6 = "CLIENT_ENCODING=WIN1250"
> Case "UTF8"
>     A6 = "CLIENT_ENCODING=UTF8"
> Case Else
>     A6 = ""
> End Select
>
> ' Connection String
>  strConnInfo = "ODBC;Driver={PostgreSQL};Server=" & SERVER & ";Port=" & PORT & ";Database=" & DATABASE & ";"
>  strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";"
>  strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" & SOCKET & ";A9=1;" & _
> "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _
> "C0=0;C1=0;C2=dd_;"
>
> strConnection = strConnInfo & strConnUserPass & strConnParms
>
> 'Checking connection
> Set qdf = db.CreateQueryDef("")
> qdf.connect = strConnection
> strSQL = "SELECT CURRENT_USER AS ""CURRENTUSER"""
> qdf.SQL = strSQL
> qdf.returnsrecords = True
> Set rs = qdf.openrecordset()
>
> CurrentUser = rs.Fields("CURRENTUSER")
>
> DoCmd.Hourglass False
>
> If CurrentUser = USERNAME Then
>     MsgBox "You have successfully connected as: " & USERNAME, , "Connection to the server"
>     ConnectionToServer = True
> Else
>     MsgBox "Connection to server failed. Check connection parameters and try again."
>     ConnectionToServer = False
>     GoTo ErrorHandler
> End If
>
>
> DoCmd.Hourglass True
>
> 'Calling functions for relinking of linked tables and adjusting connection string of pass-through queries.
>     Call RelinkLinkedTables(SERVER, DATABASE)
>     Call RelinkPassThroughQueries
>
> GoAway:
>
> DoCmd.Hourglass False
> Exit Function
>
> ErrorHandler:
>
>     Dim strErr As String
>     MsgBox "Connection to server failed. Check connection parameters and try again", , "Connection to Server"
>
>     strErr = "VBA-Error Information" & vbNewLine
>     strErr = strErr & "Number: " & vbTab & vbTab & Err.Number & vbNewLine
>     strErr = strErr & "Description: " & vbTab & Err.Description & vbNewLine
>     strErr = strErr & "LastDLLError: " & vbTab & Err.LastDllError & vbNewLine
>     strErr = strErr & vbNewLine
>     MsgBox strErr, vbOKOnly + vbExclamation, "Error"
>
>     Resume GoAway
>
> End Function
>
>
> It was working perfectly well with PostgreSQL 8.0.4 and psqlodbc-08_00_0102...
>
> What is wrong now ?
>
> Zlatko
>   ----- Original Message -----
>   From: Merlin Moncure
>   To: Zlatko Matic
>   Cc: pgsql-odbc@postgresql.org
>   Sent: Thursday, October 20, 2005 2:06 PM
>   Subject: Re: [ODBC] versions of oDBC driver
>
>
>   why not? What kind of error are you getting?  The latest revisions of the driver are probably best unless you are
usingdeclare/fetch (see archives). 
>
>
>
>   Merlin
>
>
>
>
> ------------------------------------------------------------------------------
>
>   From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of "Zlatko Matic"
>   Sent: Thursday, October 20, 2005 6:34 AM
>   To: pgsql-odbc@postgresql.org; pgsql-interfaces@postgresql.org; pgsql-general@postgresql.org
>   Subject: [ODBC] versions of oDBC driver
>
>
>
>   Hello.
>
>
>
>   Could someone say which versions of ODBC drivers are recommended for PostgreSQL/MS Access 2003 combination, for:
>
>   a) Postgres 8.0.4
>
>   b) Postgres 8.1 beta
>
>
>
>   Namely, I was not able to connect from my Access front-end when I migrated from Postgres 8.0.4 to Postgres 8.1
beta3....
>
>   Are there any significant changes that could cause such problems in connection strings ?
>
>
>
>   Zlatko
>