获取记录集值而不复制到工作表(Excel VBA)
获取记录集值而不复制到工作表(Excel VBA)
提问于 2018-07-01 01:35:34
我正在构建一个用户表单，其中用户将能够从SQL Server获得已解析查询的实时计数。
目前，我使用ADODB连接连接到SQL表并执行一个Count命令。然后，我将该值复制到一个管理工作表中，然后在用户表单中显示该值。
我想知道是否有一种方法可以在不复制到管理表单然后复制到用户表单的情况下显示计数命令结果？
我当前的代码
Private Sub UserForm_Initialize()
Set objmyconn = New ADODB.Connection
Set objmyrecordset = New ADODB.Recordset
Dim strSQL As String
objmyrecordset.CursorLocation = adUseClient
objmyconn.ConnectionString = "Provider=SQLOLEDB; Data Source=Server;Initial Catalog=DB;User ID=User;Password=Pass; Trusted_Connection=no"
objmyconn.Open
strSQL = "SELECT COUNT(TempStatus) FROM [DB] WHERE [TempStatus] = 'pinged'"
Set objmyrecordset.ActiveConnection = objmyconn
objmyrecordset.Open strSQL
Sheets("Admin").range("A1").CopyFromRecordset (objmyrecordset)
Me.Label1 = Sheets("Admin").range("A1").Value
objmyconn.Close
Set objmyconn = Nothing
Set objmyrecordset = Nothing
End Sub任何建议都将不胜感激。谢谢
*编辑*
我修改了代码，将val复制到一个变量(Test)中：
Private Sub UserForm_Initialize()
Set objmyconn = New ADODB.Connection
Set objmyrecordset = New ADODB.Recordset
Dim strSQL As String
Dim test as variant
objmyrecordset.CursorLocation = adUseClient
objmyconn.ConnectionString = "Provider=SQLOLEDB; Data Source=Server;Initial Catalog=DB;User ID=User;Password=Pass; Trusted_Connection=no"
objmyconn.Open
strSQL = "SELECT COUNT(TempStatus) FROM [DB] WHERE [TempStatus] = 'pinged'"
Set objmyrecordset.ActiveConnection = objmyconn
objmyrecordset.Open strSQL
test.CopyFromRecordset (objmyrecordset)
Me.Label1 = test.Value
objmyconn.Close
Set objmyconn = Nothing
Set objmyrecordset = Nothing
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51117146
复制相关文章
相似问题