Re: versions of oDBC driver

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