vba 通过Excel VBA查询数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10828949/
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
Query database through Excel VBA
提问by SrinR
I am a beginner in Excel VBA. I want to query data from Teradata database and give the output into the rows of an excel sheet. When i write the below code:
我是 Excel VBA 的初学者。我想从 Teradata 数据库查询数据并将输出提供到 Excel 工作表的行中。当我编写以下代码时：
Private Sub CommandButton1_Click()
Dim conn As Connection
Dim rec1 As Recordset
Dim thisSql As String
Set conn = New Connection
conn.Open "Driver=Teradata; DBCName=" & DBCName & ";UID=" & UID & ";PWD=" & PWD
thisSql = "simple select qyery here"
With .QueryTables.Add(Connection:=conn, Destination:=.Range("A1"))
.Sql = thisSql
.Name = "data"
.FieldNames = True
.Refresh BackgroundQuery:=False
End With
End Sub
I am getting the error saying 'Compiler error: User-defined type not defined'
我收到错误消息“编译器错误：未定义用户定义的类型”
how to overcome this error? Do i need to include anything in the code?
如何克服这个错误？我需要在代码中包含任何内容吗？
Please help
请帮忙
I am using MSVisualBasic 6.5 editor
我正在使用 MSVisualBasic 6.5 编辑器
回答by Jim Sjoo
Hi I guess it would need a recordset as the connection object when using QueryTables.Add. I modified your code and tried it as following:
嗨，我猜它在使用 QueryTables.Add 时需要一个记录集作为连接对象。我修改了你的代码并尝试如下：
Dim conn As adodb.Connection
Dim rec1 As adodb.Recordset
Dim thisSql As String
Set conn = New adodb.Connection
conn.Open your_connection_string
thisSql = "your query here"
Set rec1 = New adodb.Recordset
rec1.Open thisSql, conn
With Sheet3.QueryTables.Add(Connection:=rec1, Destination:=Sheet3.Range("A1"))
.Name = "data"
.FieldNames = True
.Refresh BackgroundQuery:=False
End With
