通过Excel VBA查询数据库 [英] Query database through Excel VBA

问题描述

我是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.OpenDriver = 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 Sub 
  pre> 
 
 
我收到错误说：'编译器错误：用户定义的类型未定义 
 
 
 
如何克服这个错误？ 
 
 
 
我使用的是MSVisualBasic 6.5编辑器
 p> 
 
解决方案
您好，我想使用QueryTables.Add时需要一个记录集作为连接对象。 
我修改了你的代码，并尝试如下：
  Dim conn As adodb.Connection 
 Dim rec1 as adodb.Recordset 
 Dim thisSql As String 
 
设置conn = new adodb.Connection 
 
 conn.Open your_connection_string 
 
 thisSql =您的查询在这里
 
设置rec1 =新的adodb.Recordset 
 rec1.Open thisSql，conn 
 
使用Sheet3.QueryTables.Add（Connection：= rec1，Destination ：= Sheet3.Range（A1））
 .Name =data
 .FieldNames = True 
 .Refresh BackgroundQuery：= False 
 End With 
  
 
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: 
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
解决方案
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:
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


                        
这篇关于通过Excel VBA查询数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！