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 confirmation button, a function "ConnectionToServer" is executed to: a) create connection string, b) to check whether connection string works, c) to call functions for relinking linked tables and adjusting connection string in pass-through queries. 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(at)postgresql(dot)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 using declare/fetch (see archives).
>
>
>
> Merlin
>
>
>
>
> ------------------------------------------------------------------------------
>
> From: pgsql-odbc-owner(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of "Zlatko Matic"
> Sent: Thursday, October 20, 2005 6:34 AM
> To: pgsql-odbc(at)postgresql(dot)org; pgsql-interfaces(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)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
>