Impossible to connect to PostgreSQL DB via VBA

Question

I have a problem trying to connect to my local PGSQL db via VBA. My code looks like this :

Set conn = CreateObject("ADODB.Connection") strCnx = "Driver={PostgreSQL Unicode}; Server=127.0.0.1; Database=postgres; UID=postgres;Pwd=***;port=5432" conn.Open strCnx

The error I get is

[Microsoft][ODBC Driver Controler]DATA SOURCE NAME NOT FOUND AND NO DEFAULT DRIVER SPECIFIED

I have tried a few things I found on stackoverflow, mainly installed the odbc drivers for postgresqlx64, and tried to connect to the database using directly ODBC, which told me success when I tried to connect (picture below).

Testing connection to PGSQL DB directly via ODBC

But despite everything I tried, the error is the same when I try to execute my VBA code, and I'm out of ideas, so if there are any way you could help me, I would be most thankful :)

Thanks

Frost

I installed the 64 bits drivers and tried to use the PostgreSQL Unicode driver. In the picture, the PostgreSQL35W datasource is the 64bits unicode driver (I assumed x64 stands for x86_64 bits and x86 for 32 bits, but maybe I made a mistake though ) — Frost
– Frost, Commented Aug 2, 2018 at 8:47
I'm asking if your Access application is 32-bits or 64-bits, not your ODBC driver... Those need to be matched. I deduced you used the 64-bits driver from your post. — Erik A
– Erik A, Commented Aug 2, 2018 at 8:49

Erik A · Accepted Answer · 2018-08-02 08:55:57Z

2

Make sure you install an ODBC driver with the same bitness as the application hosting your VBA. So 32-bits Excel means you need the 32-bits (x86) ODBC driver.

If you install the proper ODBC driver, this error should go away.

answered Aug 2, 2018 at 8:55

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frost Over a year ago

It works ! Thank you very much. I feel kinda dumb, I had assumed all MS office apps were in 64 bits, and that the driver was accounting for the x86/amd_64 version of PGSQL. Thanks again ! :)

Collectives™ on Stack Overflow

Impossible to connect to PostgreSQL DB via VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related