postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: fjf2002 (@fjf2002) <[email protected]>
To: postgresql-interfaces/psqlodbc <[email protected]>
Subject: [postgresql-interfaces/psqlodbc] issue #138: MS Access and postgresODBC: connection string changes
Date: Thu, 16 Oct 2025 09:26:08 +0000
Message-ID: <[email protected]> (raw)

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
```

view thread (2+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: github://postgresql-interfaces/psqlodbc
  Cc: [email protected], [email protected]
  Subject: Re: [postgresql-interfaces/psqlodbc] issue #138: MS Access and postgresODBC: connection string changes
  In-Reply-To: <<[email protected]>>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox