CODEWITHSUNDEEP
vbaexcel
Oday Salim
Oday Salim

Reputation: 1147

Getting recordset value without copying to sheet (Excel VBA)

I am putting together a userform, where a user would be able to get real-time count of resolved queries from SQL Server.

Currently, I use ADODB connection to connect to SQL table and execute a Count command. I then take the value and copy it into an Admin sheet, where it is then presented in a userform.

I wonder if there is a way to present the Count command result without copying to an Admin sheet then to the userform?

My current code

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

Any advise would be appreciated. Thanks

***EDIT****

I have amended code to copy val into a variable (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

Upvotes: 0

Views: 441

Answers (3)

Storax
Storax

Reputation: 12187

The result of "SELECT COUNT(TempStatus) FROM [DB] WHERE [TempStatus] = 'pinged'" is still a recordset. So you have to get the value of the field of the recordset like this objmyrecordset.fields(0).value. This should give you the result of COUNT.

Upvotes: 2

dbmitch
dbmitch

Reputation: 5386

You don't need CopyFromRecordset for one value

Just assign your count to an alias and use the alias fieldname from your recordset

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

Upvotes: 2

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You may try something like this...

Dim x
x = objmyrecordset.GetRows
Me.Label1 = x(0, 0)

Upvotes: 1

Related Questions