How to connect to Netezza (PureData System for Analytics) via VBA


I am trying to connect to connect to Netezza using VBA. I have enabled the following:

  • Microsoft Excel 15.0 Object Library
  • Microsoft Office 15.0 Object Library
  • Microsoft ActiveX Data Objects 6.1 Library
  • Visual Basic for Applications

Here is my code:

Sub NZConn()

Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim x As Variant

Set cmd = New ADODB.Command
Set RS = New ADODB.Recordset

cmd.ActiveConnection = "Driver={Netezza " & _
"ODBC};servername=servername;port=####;database=database;" & _

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandTimeout = 120
cmd.CommandType = adCmdText

x = "Write Query here"
cmd.CommandText = x

Set rs = cmd.Execute
Sheet1.Range("A1").CopyFromRecordset rs


End Sub

I can get the code to run without throwing back an error, but there is nothing that is pasted from the record set, which leads me to believe that is may have something to do with the structure of the connection string.

I have the server, user id, password, database, port, and driver.

Would I need to establish / open an ActiveConnection first?

I think your connection string is ok, and yes you should need to open a connection first.

Like this:

AccessConnect = "Driver={Netezza " & _
"ODBC};servername=servername;port=####;database=database;" & _

Dim Conn1 As New adodb.Connection
Conn1.ConnectionString = AccessConnect

then it would be

Set RS = Conn1.Execute(x) 'where x is your query