Monday, 28 November 2011

Connecting to postgres from Excel (visual basic)

I am trying to insert & update records from Excel (visual basic) into a postgres table but am having problems connecting to postgres.
I am able to build querries in Excel no problem.

The postgres documentation give the following example to connect...

'Open the connection
  cn.Open "DSN=<MyDataSourceName>;" & _
          "UID=<MyUsername>;" & _
          "PWD=<MyPassword>;" & _

I assume that the UID, PWD and Database are the same as the ones I would use in setting up the data source queries. the DSN is where I seem to fall down..

when I set up the datasource queries I specify "localhost" as the Server and 5432 as the Port
so I have tried using localhost:5432 as the DSN; so my code looks like:

Sub UpdateRecord()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

'Open the connection
  cnn.Open "DSN=localhost:5432;" & _
          "UID=postgres;" & _
          "PWD=password;" & _
End Sub

as the DSN but get
"Run Time Error 91"
"Object variable or With block variable not set

any ideas on what I am doing wrong??