Message-ID: <gh-postgresql-interfaces-psqlodbc-138@github.com>
From: "fjf2002 (@fjf2002)" <noreply+fjf2002@github.com>
To: "postgresql-interfaces/psqlodbc" <noreply+postgresql-interfaces-psqlodbc@github.com>
Date: Thu, 16 Oct 2025 09:26:08 +0000
Subject: [postgresql-interfaces/psqlodbc] issue #138: MS Access and postgresODBC: connection string changes
List-Id: <gh-postgresql-interfaces-psqlodbc.github.com>
X-GitHub-Author-Id: 7130250
X-GitHub-Author-Login: fjf2002
X-GitHub-Issue: 138
X-GitHub-Repo: postgresql-interfaces/psqlodbc
X-GitHub-State: open
X-GitHub-Type: issue
X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/138
Content-Type: text/plain; charset=utf-8

Hello,
I am using MS Access with postgresODBC and I'm not sure which of both is responsible for the following behaviour.
However I **did not** experience this behaviour in the past with MS SQL Server.

### Experienced behaviour:
* The connection string changes when VBA `db.TableDefs.Append` is called.
* However it does not change when `tableDef.RefreshLink` is called

### Expected behaviour:
The connection string shall not change.

### Remark 1
I am using the parameters BoolsAsChar=0 and TrueIsMinus1=1, thus **NOT USING** their default value, cf.
* https://odbc.postgresql.org/docs/config-opt.html
* https://www.enterprisedb.com/docs/odbc_connector/latest/05_edb-odbc_connection_properties/

I could somehow imagine that the connection string is being transformed into a canonical form, perhaps omitting properties that use default values.
However this is not the case:
* BoolsAsChar=0 and TrueIsMinus1=1 are not the default values
* Neither BoolsAsChar/TrueIsMinus1 nor their short equivalents B9/C6 occur in the changed connection string.


### Remark 2
This ticket is just about the connection string changing. But what properties really apply to the connection?
I suspected the ORIGINAL connection string properties, NOT the changed ones.

### Remark 3
I even had problems about MS ACCESS connecting to the **WRONG** Postgres Server.
I had two Postgres Servers running on the same host on different ports.
But currently I cannot reproduce that.


### Test Case
```vba
Sub testConnString()
    Const connString = "ODBC;DRIVER={PostgreSQL UNICODE};Server=myserver;Port=5433;Database=pvz;sslmode=require;Trusted=true;BoolsAsChar=0;TrueIsMinus1=1;pqopt={application_name=bar.accdb}"
    
    Dim db As Database: Set db = CurrentDb
    Dim td As tableDef: Set td = db.CreateTableDef
    td.name = "foo"
    td.sourceTableName = "foo"
    td.connect = connString
    db.TableDefs.Append td
    
    Debug.Print db.TableDefs("foo").connect
    ' prints:
    ' ODBC;DRIVER={PostgreSQL UNICODE};DATABASE=pvz;SERVER=myserver;PORT=5433;UID=myuser;PWD=;CA=r;A7=100;B0=255;B1=8190;BI=0;C2=;D5={application_name=bar.accdb};D6=-101;CX=1c381008b;A1=7.4
    ' what the heck!?
    
    db.TableDefs("foo").RefreshLink
    Debug.Print db.TableDefs("foo").connect ' still prints the changed connection string
    
    db.TableDefs("foo").connect = connString
    Debug.Print db.TableDefs("foo").connect ' now prints connString
    
    db.TableDefs("foo").RefreshLink
    Debug.Print db.TableDefs("foo").connect ' still prints connString
End Sub
```