如何通过VBA连接到Netezza (PureData分析系统)

Question

我正在尝试用VBA连接到Netezza。我做到了以下几点：

• Microsoft 15.0对象库
• MicrosoftOffice15.0对象库
• 微软ActiveX数据对象6.1库
• 用于应用程序的Visual Basic

这是我的代码：

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

我可以在不抛出错误的情况下运行代码，但是没有从记录集中粘贴的东西，这使我相信这可能与连接字符串的结构有关。

我有服务器、用户id、密码、数据库、端口和驱动程序。

我需要先建立/打开一个ActiveConnection吗？

Answer 1

我自己解决了这个问题。我发现在Aginity的“Tools”选项卡中有一个命令行生成器，它帮助指定了连接到Netezza所需的确切连接字符串。一旦我有了这个连接字符串，我就会得到一个“架构不匹配”错误。在下载了Netezza的32位ODBC驱动程序之后，这个方法非常有效。以下是更新的代码：

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

注：

• "IBM NETEZZA ODBC驱动程序- 32位“是我下载的
• “ODBC-driver-fornetezza-7-x86”出现在我的软件中心来安装。
• “名称: NetezzaSQL；版本: 7.00.04.41188；Company: www.ibm.com；File: NSQLODBC.DLL”是现在我的32位'ODBC数据源管理员‘窗口中显示的内容。

Answer 2

我认为您的连接字符串是好的，是的，您应该首先打开一个连接。

如下所示：

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

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

那就会是

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