Excel VBA connect to postgres with run-time error at recordset.Open

Trying to connect to a postgres database in 32 bit Excel (2016) VBA I get the runtime error, when the code comes to the line with the recordset being opened to pass through an SQL string:

(in german)

Laufzeitfehler '-2147467259 (80004005)':

Automatisierungsfehler

Unbekannter Fehler

(can be translated to)

Runtime error '-2147467259 (80004005)':

error in automation

unknown error

My code looks like this:

Sub go()

Dim cn As Object
Dim rs As Object

Dim name As String
Dim pwd As String
Dim ip As String
Dim db As String

name = "postgres"
pwd = "secretPwd"
ip = "192.168.42.42"
db = "test"
   
Set cn = CreateObject("ADODB.Connection")
cn.Open "Driver={PostgreSQL ANSI};" & "DSN=PostgreSQL30;" & "Database=" & db & ";" & "SERVER=" & ip & ";" & "Uid=" & name & ";" & "Pwd=" & pwd

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM hi", cn

'rs.Close
'cn.Close

End Sub

The postgres database name and location seem to be correct and found. In the 32 bit ODBC data source admin tool I was able to add a DNS and test the inserted data succesfully.

With the tool PGAdmin I was able to test the SQL string "SELECT * FROM hi" and there I get some resulting rows.

The VBA code works until the Open Method of the record set. There I get a runtime error. The ADODB.Connection object can be opened - apparently. If I use another DSN name, which is not defined via the 32 bit ODBC admin tool, it still seems to work. If I change the ip-Address or database name, I get the runtime error when calling the Open method of the ADODB.Connection object (which makes sense). Using "PostgreSQL UNICODE" as Driver string does work - changing it to something random causes a runtime error at this point. But I do not understand, why a randow DSN string does not cause a failure.

If the code goes beyond the Open method of the ADODB.Connection object, it fails at the Open method of the ADODB.RecordSet.

How can I check, if the ADODB.Connection object really is working? Is there another reason, why the Open method of the ADODB.RecordSet fails?

Solution

I've never used Postgres but is it possible you need to scope that table name hi with some kind of domain/schema? PGAdmin may do that for you automatically.

See for example Set deafult schema while querying in pgAdmin 4 with query tool

select * from SchemaHere.hi