0

I have been running queries in SQL in Python using psycopg2. This is very simple and I just do the following:

conn2 = psycopg2.connect('postgresql://YourUserName:YourPassword@YourHost:5432/YourDatabase')
query = "xxx"
query_output = pd.read_sql_query(query, conn2).drop_duplicates(keep='first')

Is it possible to something similarly as easy in VBA? I am hoping to create an easy-to-use excel add-in so want to avoid asking the user to have to set up 32-bit drivers in ODBC Driver Manager.

I am trying to develop a setup here:

Dim objDb_con
Dim strSomeValue As String

Set objDb_con = CreateObject("ADODB.Connection")
Set Rsdatatype = CreateObject("ADODB.RecordSet")

glbConnString = "Driver={PostgreSQL UNICODE};Database=XX;port=XX;server=XX;UID=XX;Pwd=XX;"

objDb_con.Open glbConnString

But I get the error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Looking around it appears this is to do with ODBC driver managers and even though I tried to set-up the default as 32-bit I still get this error.

Is there a psycopg2-style alternative for VBA that is simple and doesn't require set-up for the user?

GBA111
  • 1
  • 1
  • You are seeking recommendations for books, tools, software libraries, and more which is off-topic on StackOverflow to avoid spam and opinion-based answers. – Parfait Jul 21 '20 at 16:13
  • But I will say this, `psycopg2` is a native Python-PostgreSQL DB-API. The best counterpart to your ADO-VBA attempt is Python's `pyodbc` DB-API which requires all users to install an appropriate PostgreSQL ODBC driver to conform to his/her OS and 32/64-bit architecture. AFAIK, there is no native Excel-Postgres API. Usually, in all MS Office applications, connections to relational databases are either via ODBC or OLEDB. – Parfait Jul 21 '20 at 16:14
  • @Perfait. Okay thank you, my apologies - I'm new here and didn't quite understand the rules. That you say that "Usually, in all MS Office applications, connections to relational databases are either via ODBC or OLEDB" is very helpful though. Thank you – GBA111 Jul 21 '20 at 16:25
  • This (and the linked infos) might help: https://stackoverflow.com/questions/13230456/connecting-excel-to-postgresql-via-vba Btw: I don't think that this question is off-topic, off-topic in this sense would be to ask *"what is the best database"* or *"what is the best book to learn VBA"* or *"why is Python superior to VBA"*. – FunThomas Jul 21 '20 at 16:50

1 Answers1

0

I always pull connection strings from this website:

https://www.connectionstrings.com/progress/

They clearly show that there is no built-in .NET libraries or OLE DB providers for PostGress. Thus, you cannot get around installing the driver. You could package an installer to automate this process.

If you have the ODBC drivers installed you would use a connection string like this:

Standard

Include only the parameters you want to override from the system DSN settings

DSN=myDSN;HOST=myServerAddress;DB=myDataBase;UID=myUsername;PWD=myPassword;PORT=2055;

Alternative using long names

Include only the parameters you want to override from the system DSN settings

DataSourceName=myDSN;HostName=myServerAddress;Database=myDataBase;LogonID=myUsername;Password=myPassword;PortNumber=2055;
HackSlash
  • 4,944
  • 2
  • 18
  • 44