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

Question

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?

merely a guess but does it make a difference if you add a command type parameter, e.g. rs.Open "SELECT * FROM hi", cn, ,1 ( its been a while but I think with vba you can leave the locktype parameter as that empty one in the middle :) ) — topsail
– topsail, Commented Feb 24 at 22:54
"I get a runtime error" - please include the exact text of the error you get. "DSN" does not appear as an attribute in any of the connection strings listed here connectionstrings.com/postgresql so what documentation are you following? — Tim Williams
– Tim Williams, Commented Feb 24 at 23:05
The runtime error is in the question, now. I copied the Open method of the ADODB.Connection object and experimented partly with it from here stackoverflow.com/questions/9117516/… and changed the string after "DSN=" to what is seen in the 32 bit ODBC data source admin tool under "data source name". The tool itself proposed this name after selecting a corresponding driver. — bejo
– bejo, Commented Feb 24 at 23:13
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 stackoverflow.com/questions/45714927/… Eg select * from SchemaHere.hi — Tim Williams
– Tim Williams, Commented Feb 25 at 17:41

Tim Williams · Accepted Answer · 2025-02-25 23:02:51Z

1

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

Eg

select * from SchemaHere.hi

answered Feb 25 at 23:02

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related