CODEWITHSUNDEEP
sqlvbaexcelodbcnetezza
Torc
Torc

Reputation: 1322

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:

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;" & _
"username=username;password=password;"

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

cmd.ActiveConnection.Close

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?

Upvotes: 0

Views: 3666

Answers (2)

Torc
Torc

Reputation: 1322

I was able to figure out the issue on my own. I found that there is a command line builder in the 'Tools' tab in Aginity, which helped specify the exact connection string I needed to connect to Netezza. Once I had this connection string, I was getting an 'architecture mismatch' error. After downloading the 32-bit ODBC drivers for Netezza, the methodology worked perfectly. Here is the updated code below:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset        
Dim iCols As Integer             
Dim DB As String, User As String, PW As String, ConnectionString As String
Dim Server As String, Query As String
Dim SQLTable As Worksheet

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set SQLTable = Sheet1

Server = SQLTable.Cells(2,3).Value
User = SQLTable.Cells(2,4).Value
PW = SQLTable.Cells(2,5).Value
DB = SQLTable.Cells(2,6).Value
Query = SQLTable.Cells(2,7).Value

ConnectionString = "Driver={NetezzaSQL};" & _
                   "server=" & Server & ";" & _
                   "UserName=" & User & ";" & _
                   "Password=" & PW & ";" & _
                   "Database=" & DB & ";" & _
                   "Query Timeout=120"

cn.Open (ConnectionString)
rs.Open (Query), cn

For iCols = 0 To RS.Fields.count - 1
    Worksheets("Sheet2").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
Worksheets("Sheet2").Cells(2, "A").CopyFromRecordset rs

rs.Close
cn.Close

NB:

  • "IBM NETEZZA ODBC DRIVER – 32 BIT" is what I downloaded
  • "ODBC-DRIVER-FOR-NETEZZA-7-X86" is what showed up in my software center to install
  • "Name: NetezzaSQL ; Version: 7.00.04.41188 ; Company: www.ibm.com ; File: NSQLODBC.DLL" is what is shown now in my 32-bit 'ODBC Data Source Administrator' window

Upvotes: 2

kltft
kltft

Reputation: 3793

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;" & _
"username=username;password=password;"

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

then it would be 

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

Upvotes: 1

Related Questions