Hello.
After I tried different things, I finally figured out where is the problem
with connection string: "Driver={PostgreSQL}" must be changed to
"Driver={PostgreSQL Unicode}". Now it works.
But this new connection string works only with Postgres 8.1, while it
doesn't work with Postgres 8.0...
I must say that current documentation lacks with information about
connection string parameters. Everything I could find about it was quite old
and not sufficient. It would be really nice if someone competent would write
some document regarding ODBC connection string parameters to explain their
meaning for "dummies" like me. I suppose I'm not the only one trying to use
MS Access with PostgreSQL. I think it is quite good combination for
hobbiests and people who are not programmers but want to create some
specific solutions for their job. MS Access is very easy to use and learn
and is widespread also, so supporting people in such efforts would
definitely increase popularity of PostgreSQL. Understanding ODBC connection
string parameters is essential for that.
Regarding connection parameters, for example, I would kindly ask you to tell
me which parameters are not neccessary in my connection string, so that I
can remove it, in order to allow more space in connection string. You have
already mentioned: "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.", so I would like to optimize my connection string to allow more
space for really important parameters in limited connection string in
Access...
This is my connection string:
strConnInfo = "ODBC;Driver={PostgreSQL Unicode};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
What can I remove from it?
Thanks,
Zlatko
----- Original Message -----
From: "Greg Campbell" <greg.campbell@xxxxxxxxxxxxxxx>
To: "Zlatko Matić" <zlatko.matic1@xxxxxxxxxxx>
Cc: <merlin.moncure@xxxxxxxxxxxxx>; <pgsql-odbc@xxxxxxxxxxxxxx>
Sent: Friday, October 21, 2005 5:12 PM
Subject: Re: [ODBC] 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@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx
[mailto:pgsql-odbc-owner@xxxxxxxxxxxxxx] On Behalf Of "Zlatko Matic"
Sent: Thursday, October 20, 2005 6:34 AM
To: pgsql-odbc@xxxxxxxxxxxxxx; pgsql-interfaces@xxxxxxxxxxxxxx;
pgsql-general@xxxxxxxxxxxxxx
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
--------------------------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
