• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 66
  • Last Modified:

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
0
sidwelle
Asked:
sidwelle
  • 4
  • 2
1 Solution
 
Ryan ChongCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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 ChongCommented:
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
 
sidwelleAuthor Commented:
Thanks for the help.

All seems to be working.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now