sql - 我正在尝试使用来自 SQL 的查询填充我的 ComboBox

Question

首先，我是 VB 新手，因此非常感谢所有帮助。

我正在尝试使用来自 SQL 服务器的数据填充 Excel 中的组合框。

错误是“Dim cnt As ADODB.Connection” - 编译错误：未定义用户定义的类型

Sub Populate_ComboBox_From_SQL()
 Dim cnt As ADODB.Connection
 Dim rst As ADODB.Recordset
 Dim stDB As String, stConn As String, stSQL As String
 Dim xlCalc As XlCalculation
 Dim vaData As Variant
 Dim k As Long

 Set cnt = New ADODB.Connection

 stConn = "DSN=Backup;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=<database>"
 cnt.ConnectionString = stConn


 stSQL = "SELECT 'project no' FROM 'project register"

 With cnt
 .CursorLocation = adUseClient 
 .Open stConn 'Open connection.

 Set rst = .Execute(stSQL)
 End With

 With rst
 Set .ActiveConnection = Nothing 'Disconnect the recordset.
 k = .Fields.Count

 vaData = .GetRows
 End With


 cnt.Close


 With TEMPLATE
 With .ComboBox1
 .Clear
 .BoundColumn = k
 .List = Application.Transpose(vaData)
 .ListIndex = -1
 End With
 End With


 Set rst = Nothing
 Set cnt = Nothing
End Sub

该工作表称为 TEMPLATE，而 ComboBox 称为 ComboBox1。我在连接字符串中省略了 SQL 服务器的名称。

提前致谢。

标签： sqlexcelvba