如何在不复制到工作表的情况下获取记录集值(ExcelVBA)?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (36)

我正在组合一个userform,用户可以从SQL Server获得已解析查询的实时计数。

目前,我使用ADODB连接来连接SQL表并执行Count命令。然后我获取值并将其复制到管理表单,然后在用户表单中显示它。

我想知道是否有一种方法可以显示Count命令结果而无需复制到Admin表然后复制到userform?

我目前的代码

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
提问于
用户回答回答于

结果"SELECT COUNT(TempStatus) FROM [DB] WHERE [TempStatus] = 'pinged'"仍然是记录集。所以你必须像这样得到记录集的字段的值objmyrecordset.fields(0).value。这应该给你的结果COUNT

用户回答回答于

你不需要CopyFromRecordset

只需将计数分配给别名,并使用记录集中的别名字段名即可。

strSQL = "SELECT COUNT(TempStatus) AS CountRecs FROM [DB] WHERE [TempStatus] = 'pinged'"
Set objmyrecordset.ActiveConnection = objmyconn
objmyrecordset.Open strSQL
test = objmyrecordset!CountRecs
Me.Label1 = test

扫码关注云+社区

领取腾讯云代金券