Connecting to Postgress from VBA

Postgress SQL via VBA.

What should my connection sting look like ?

I have:
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection

    Dim strConn As String
    'strConn = "Driver={SQL Server};Server=; Database=; UID=; PWD="
    'strConn = "Driver={PostgreSQL Unicode(x64)};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW
    'strConn = "Driver={PostgreSQL35W};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW
    strConn = "Driver={PostgreSQL};Server=" & srv & "; Database=" & sDB & "; UID=" & sID & "; PWD=" & sPW

    cn.Open strConn

Open in new window

But all those options always produce the same error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

System DNSUser DNS
sidwelleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
first, make sure you have installed the relevant database driver into your machine?
for example:
https://www.postgresql.org/ftp/odbc/versions/msi/

second, try use the connection string as stated at:

PostgreSQL connection strings
https://www.connectionstrings.com/postgresql/
0
sidwelleAuthor Commented:
Made some changes based on the site you posted, like changing from 'Driver' to 'Provider', but now I get a different error.
Provider cannot be found. It may not be properly installed.

I am using Office Excel Home and Business edition 2016.
Assuming its the 64 bit edition, but I don't see anywhere the it tells you.
0
sidwelleAuthor Commented:
Installed both 32 and 64 editions, still no success.
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

sidwelleAuthor Commented:
Did some reading:
** Must be the 32 bit version of Postgress Driver.
This was the version of the conn string that worked, others would probably have worked, but I didn't mess w/it any more.
Driver={PostgreSQL Unicode};Server= ...
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Provider cannot be found. It may not be properly installed.
yea, should be the provider (driver need to be installed), so appropriate provider should be installed and provided in the connectionstring.

sorry that didn't use PostgreSQL at work so can only provide you some guidance instead of a real example here.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sidwelleAuthor Commented:
Thanks for the help.

All seems to be working.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.