无法使用 VBA 代码从 Excel 连接到 Teradata - 无法通过网络访问 Teradata 服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28379191/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unable to connect to Teradata from excel using VBA code - Teradata Server can't be reached over the network
提问by Vijayshankar
I have been trying to connect to Teradata from Excel using vba code but I am getting the below mentioned error: Teradata Server can't be reached over the network. I have been able to successfully connect from Teradata SQL assistant and I also successfully pinged the Teradata server(I am using Teradata express edition in my laptop which runs on VMware player). I also successfully connected to Teradata from Excel Data connection wizard and Microsoft query. I have also switched off the firewall and checked to see If I am able to connect but still no luck in connecting. Please let me know where I am going wrong.
我一直在尝试使用 vba 代码从 Excel 连接到 Teradata，但出现以下错误：无法通过网络访问 Teradata 服务器。我已经能够从 Teradata SQL 助手成功连接，并且我也成功 ping 了 Teradata 服务器（我在我的笔记本电脑中使用 Teradata express 版本，它在 VMware 播放器上运行）。我还从 Excel 数据连接向导和 Microsoft 查询成功连接到 Teradata。我还关闭了防火墙并检查了我是否能够连接但仍然没有连接成功。请让我知道我哪里出错了。
Please find below code:
请找到以下代码：
Private Sub CommandButton1_Click()
Dim conn As ADODB.Connection
Dim rec1 As ADODB.Recordset
Dim thisSql As String
Set conn = New ADODB.Connection
conn.Open "Driver=Teradata;DBCName=dsnname;Databasename=dbname;Uid=Userid;Pwd=****;"
thisSql = "sel * from customer_db.customer"
Set rec1 = New ADODB.Recordset
rec1.Open thisSql, conn
With Sheet1.QueryTables.Add(Connection:=rec1, Destination:=Sheet2.Range("A1"))
.Name = "data"
.FieldNames = True
.Refresh BackgroundQuery:=False
End With
End Sub
采纳答案by Rob Paller
What if you change the Connection object's Open method:
如果更改 Connection 对象的 Open 方法会怎样：
conn.Open "DSN=dsnname;Databasename=dbname;Uid=Userid;Pwd=****;"
conn.Open "DSN=dsnname;Databasename=dbname;Uid=Userid;Pwd=****;"
回答by David
This connection script worked for me.
这个连接脚本对我有用。
' Add Microsoft ActiveX Data Objects 2.8 Library in References ' When installing Teradata SQL Assistant, include the ODBC Driver for Teradata will install the TDOLEDB provider ' This example connects to Teradata, deletes the contents of MyTable & inserts row 7- 8 from the active spreadsheet
' 在参考中添加 Microsoft ActiveX 数据对象 2.8 库 ' 安装 Teradata SQL 助手时，包括 Teradata 的 ODBC 驱动程序将安装 TDOLEDB 提供程序 ' 此示例连接到 Teradata，删除 MyTable 的内容并从活动电子表格中插入第 7-8 行
Function OpenConn() As Object Set OpenConn = New ADODB.Connection Dim myConnectionString As String myConnectionString = "Provider=TDOLEDB;Data Source=MyTeradataServerName;Persist Security Info=True;User ID=MyTeradataUserID;Password=MyTeradataPass;Session Mode=ANSI;DefaultDatabase=GRP_BCE_FINANCE_IM;MaxResponseSize=65477;" OpenConn.Open myConnectionString End Function
Function OpenConn() As Object Set OpenConn = New ADODB.Connection Dim myConnectionString As String myConnectionString = "Provider=TDOLEDB;Data Source=MyTeradataServerName;Persist Security Info=True;User ID=MyTeradataUserID;Password=MyTeradataPass;Session Mode=ANSI;DefaultDatabase =GRP_BCE_FINANCE_IM;MaxResponseSize=65477;" OpenConn.Open myConnectionString 结束函数
Sub CloseConn(conn As Object) conn.Close Set conn = Nothing End Sub
Sub CloseConn(conn As Object) conn.Close Set conn = Nothing End Sub
Sub PushCCHier()
子 PushCCHier()
Dim TeraObjCmd As New ADODB.Command
Dim TeraObjRs As ADODB.Recordset
Dim TeraObjRs2 As ADODB.Recordset
Dim TeraCnxn As Object
Set TeraCnxn = OpenConn()
TeraObjCmd.ActiveConnection = TeraCnxn
'Clear Previous Data
TeraObjCmd.ActiveConnection = TeraCnxn
TeraObjCmd.CommandText = "delete from MyTable"
TeraObjCmd.Execute
'Load New Data
Set TeraObjRs2 = New ADODB.Recordset
TeraObjRs2.Open "SELECT * FROM MyTable where 1 = 2 ", TeraCnxn, adOpenStatic, adLockOptimistic
With TeraObjRs2
For irow = 7 To 8 'loading results from rows in my spredsheet
If Len(Trim(Range("B" & irow).Value)) <> 0 Then 'Avoid blank rows
.AddNew
.Fields(0) = Range("B" & irow).Value
End If
Next
.UpdateBatch
.Close
End With
' clean up objects
Set objCmd = Nothing
Call CloseConn(TeraCnxn)
MsgBox "Update Complete!"
End Sub
结束子
