我正在构建一个用户表单,其中用户将能够从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
发布于 2018-07-01 02:07:45
"SELECT COUNT(TempStatus) FROM [DB] WHERE [TempStatus] = 'pinged'"
的结果仍然是一个记录集。因此,您必须像这样获取记录集的字段的值objmyrecordset.fields(0).value
。这应该会给出COUNT
的结果。
发布于 2018-07-01 02:07:18
您不需要为一个值使用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
发布于 2018-07-01 02:06:35
你可以试试这样的东西...
Dim x
x = objmyrecordset.GetRows
Me.Label1 = x(0, 0)
https://stackoverflow.com/questions/51117146
复制相似问题