vba 如何使用来自 SQL Server 的数据填充 Excel ComboBox？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22016591/
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
How to populate Excel ComboBox with data from SQL Server?
提问by Almazini
I am trying to populate a combobox in Excel file with data from SQL Server.
我正在尝试使用来自 SQL Server 的数据填充 Excel 文件中的组合框。
Here is code for event:
这是事件的代码：
Private Sub Workbook_Open()
ActiveWorkbook.Sheets("Generation").Activate
Set cn = New ADODB.Connection
On Error Resume Next
With cn
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Server=" & "192.160.160.150;" & _
"Database=" & "em_Consumer;" & _
"User Id= " & "User" & _
"Password = " & "server123"
.Open
End With
Set rs = New ADODB.Recordset
sqltextexec = " SELECT name FROM sys.tables WHERE schema_id = 7 AND name LIKE 'FinalCalculated%' ORDER BY create_date "
rs.Open sqltextexec, cn
rs.MoveFirst
With Sheets("Generation").ComboBox1
.Clear
Do
.AddItem rs![Name]
rs.MoveNext
Loop Until rs.EOF
End With
End Sub
This code works on my computer and on my colleague's as well (we are from DB team) but analysts who don't work with DB don't get list populated in the file.
此代码适用于我的计算机和我同事的计算机（我们来自 DB 团队），但不使用 DB 的分析师不会在文件中填充列表。
Is it possible the program uses Windows authentication to connect to the DB?
程序是否可以使用 Windows 身份验证连接到数据库？
回答by Lopsided
Connection String Error
连接字符串错误
It seems there is an error in your connection string. The user ID needs to have a semi-colon after it. Change this
您的连接字符串中似乎有错误。用户 ID 后面需要有一个分号。改变这个
With cn
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Server=" & "192.160.160.150;" & _
"Database=" & "em_Consumer;" & _
"User Id= " & "User" & _
"Password = " & "server123"
To this
对此
With cn
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Server=" & "192.160.160.150;" & _
"Database=" & "em_Consumer;" & _
"User Id= " & "User;" & _
"Password = " & "server123"
That was an elusive little bugger.
那是一个难以捉摸的小家伙。
Edit
编辑
I'm having trouble pinpointing the issue here, so perhaps a working example will better assist you at this point...
我在这里查明问题时遇到了麻烦，所以在这一点上，一个有效的例子可能会更好地帮助你......
Function getSqlData(queryString As String, myUsername As String, myPassword As String, database As String) As Recordset
Dim conn As New ADODB.Connection
Dim rst As Recordset
Dim serverName As String
serverName = "192.160.160.150"
With conn
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=" & serverName & ";" & _
"Initial Catalog=" & database & ";User Id=" & myUsername & ";" & _
"Password=" & myPassword & ";Trusted_Connection=no"
.Open
End With
Set rst = conn.Execute(queryString)
Set getSqlData= rst
End Function
This will return your recordset.
这将返回您的记录集。
回答by Almazini
today I tried to write it from scratch using @lopsided help. Here is the code:
今天我尝试使用@lopside 帮助从头开始编写它。这是代码：
Private Sub Workbook_Open()
ActiveWorkbook.Sheets("generation").Activate
Dim rstt As Recordset
MsgBox "1"
Set rstt = getData()
End Sub
-------------------------------------------------
Private Function getData()
Dim conn As New Connection
Dim rst As Recordset
Dim sqlstring As String
Dim rwcnt As Integer
MsgBox "2"
sqlstring = "SELECT productname FROM dbo.products WHERE recalc = 1"
With conn
.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=192.160.160.150;" & _
"Initial Catalog=em_Consumer;" & _
"User Id=User;" & _
"Password=server!;" & _
"Trusted_Connection=no"
.Open
End With
MsgBox "3"
Set rst = conn.Execute(sqlstring)
rwcnt = rst.RecordCount
MsgBox rwcnt
MsgBox "5"
Set getData = rst
MsgBox "6"
End Function
So when i open the file I get messages:
所以当我打开文件时，我收到消息：
1 which means that program started;
1 表示程序已启动；
2 which means that it entered the function;
2 表示进入了函数；
3 which means that there is no issues with connection;
3 表示连接没有问题；
!! then I get -1 value as record count which means that something is wrong
!! 然后我得到 -1 值作为记录计数，这意味着出现问题
I tried to run this query in management studio and it returns 50 rows
我试图在管理工作室中运行这个查询，它返回 50 行
Then program goes further and I get 5 and 6 ...
然后程序更进一步，我得到 5 和 6 ...
Do you have any ideas what is wrong with the code?
你知道代码有什么问题吗？
---------------------------------------------
---------------------------------------------
Maybe it can help, code which works fine but returns table not recordset in the same document:
也许它可以提供帮助，代码可以正常工作但在同一文档中返回表而不是记录集：
Sub Button3_Click()
ActiveSheet.Cells.Clear
Dim qt As QueryTable
sqlstring1 = "SELECT * FROM dbo.Report"
With ActiveSheet.QueryTables.Add(Connection:=getConnectionStr2, Destination:=Range("A3"), Sql:=sqlstring1)
.Refresh
End With
End Sub
----------------------------------
Private Function getConnectionStr2()
'DRIVER={SQL Server};
getConnectionStr2 = "ODBC;DRIVER={SQL Server};" & _
"DATABASE=em_Consumer;" & _
"SERVER=192.160.160.150;" & _
"UID=user;" & _
"PWD=server!;"
End Function
