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>;" & _
          "Database=<MyDatabaseName>"


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;" & _
          "Database=postgres"
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??

4 comments:

  1. Your DSN is wrong. But you knew that. You are using the ODBC driver and the DSN refers to the name you registered your PostgreSQL data source in ODBC manager (not the PostgreSQL server).

    So if you registered it as MyLocalPgServer, then that would be what you use for DSN

    ReplyDelete
  2. Did you try connecting to the Postgres database via Excel's get external data, data source GUI and/or, as Regina commented, creating a datasource that connects to the database? I haven't used the VBA approach but I've used the former to get PostgreSQL query results into Excel for charting. I haven't used VBA because there's a lot more functionality available in PostgreSQL queries and procedures.

    ReplyDelete
  3. For whatever weird reason, your

    "PWD=password;"

    line got turned into a hyperlink with the contents

    mailto:PWD=!Q@aw3se4

    Ah...the @ sign. Microsoft copy and paste? Or some other?

    ReplyDelete
  4. I have solved the Excel to Postgres using VB connection problem.
    MS query is fine for doing reads from your database but it is sometimes usefull to write records from excel.

    Here for others to use is the macro..
    Sub UpdateRecord()
    Dim cnn As New ADODB.connection
    Dim rst As New ADODB.Recordset
    'dim cnn.ConnectionString string

    cnn.Open "Driver={PostgreSQL ODBC Driver(Unicode)};DSN=postgres;Server=localhost;Port=5432;UID=postgres;PWD=password; Database=postgres;" & _
    "READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=1"

    'dropTable = "DROP TABLE IF EXISTS nyse.blobtable"
    'createTable = "CREATE TABLE nyse.blobtable (id INTEGER , myblob char(2), myBlobName VARCHAR(300))"
    until
    insertTable = "INSERT INTO nyse.customer" & _
    " VALUES (customer.number,customer.name,customer.addr1,customer.addr2,customer.city,customer.postalcode,customer.country)"

    ' Execute queries and prepare blob writing

    dropResult = cnn.Execute(dropTable)
    createResult = cnn.Execute(createTable)
    insertResult = cnn.Execute(insertTable)

    cnn.Close

    End Sub

    I have used the add and drop table commands just to illustrate that you can do almost anything in the database from excel.

    ReplyDelete