SQL connetion to check number of rows before pasting the data

[drom]

Hi and Thanks in advance!

I am connecting to Through VBA-SQL to a external database (Works OK on my Macro1_SqlConnection Macro)
I am Pasting the Data to a Range (works fine)

I am using the following macro, and Works PERFECT, (without checking the number os rows)

Sub Macro1_SqlConnection(wSql As String, wProvider As String, wDataSource As String, wInitialCatalog As String, wUserID As String, wPwd As String, rToPasteTheData As Range)
Dim objConn As New ADODB.connection
Dim objRs As New ADODB.Recordset
Dim objRsTemp As New ADODB.Recordset

Dim wConnection As String:              wConnection = _
                                            "Provider=" & wProvider & ";" & _
                                            "Data Source=" & wDataSource & ";" & _
                                            "Initial Catalog=" & wInitialCatalog & ";" & _
                                            "User ID=" & wUserID & ";" & _
                                            "Pwd=" & wPwd

                                        objConn.Open wConnection
                                        objRs.Open wSql, objConn

rToPasteTheData.CopyFromRecordset objRs

End Sub

But How Can I check the Number of rows before pasting anything ??

Sometimes the Data source contains many Rows, so prior pasting the Data, I would like to know how many rows I am going to paste

So I am trying to Use:

Sub Macro2_SqlConnection(wSql As String, wProvider As String, wDataSource As String, wInitialCatalog As String, wUserID As String, wPwd As String, rToPasteTheData As Range)
Dim objConn As New ADODB.connection
Dim objRs As New ADODB.Recordset
Dim objRsTemp As New ADODB.Recordset

Dim wConnection As String:              wConnection = _
                                            "Provider=" & wProvider & ";" & _
                                            "Data Source=" & wDataSource & ";" & _
                                            "Initial Catalog=" & wInitialCatalog & ";" & _
                                            "User ID=" & wUserID & ";" & _
                                            "Pwd=" & wPwd

                                        objConn.Open wConnection
                                        objRs.Open wSql, objConn

Dim varArrayReader():                   varArrayReader() = objRs.GetRows

  Debug.Print UBound(varArrayReader, 2), UBound(varArrayReader, 1) ' I get 1590 5
  'So I can create an array with the Data ....
  'But when doing this:
  If UBound(varArrayReader, 2) < 5000 Then rPastedPlace.CopyFromRecordset objRs
  'The Error keeps 0 But I am not able to paste anything
  'and I do not want to create an array with the data if not necessary
  
  If objRs.State = adStateOpen Then objRs.Close
  If objConn.State = adStateOpen Then objConn.Close
 
End Sub

How Can I get the Number of Rows ??

 

[drom]

Debug.Print objRs.Fields.Count
Is giving me the columns Count
But I Do not know the Rows Rount ??

 

[nguyendang]

You can get rows count by setting objRs.CursorLocation to adUserClient before calling .Open method of objRs, then Debug.Print objRs.RecordCount (not always reliable).
Or, you can transpose objRs.GetRows, then get rows count with UBound (this will create an unnecessary array):

varArrayReader = Transpose2DArray(objRs.GetRows)
Debug.Print UBound(varArrayReader)
'Paste the array into the specified range
rToPasteTheData .Offset(0, 0).Resize(UBound(arrResult) + 1, UBound(arrResult, 2) + 1).Value = varArrayReader

Private Function Transpose2DArray(ByVal InputArray As Variant) As Variant
    Dim arrResult() As Variant
    Dim i As Long, j As Long
    Dim lngMaxColumnIndex As Long
    On Error Resume Next
    lngMaxColumnIndex = UBound(InputArray, 2)
    If Err.Number = 9 Then
        Err.Clear
        Transpose2DArray = InputArray
        Exit Function
    End If
    ReDim arrResult(1 To UBound(InputArray, 2) + 1, 1 To UBound(InputArray) + 1)
    For i = 1 To UBound(InputArray) + 1
        For j = 1 To UBound(InputArray, 2) + 1
            arrResult(j, i) = InputArray(i - 1, j - 1)
        Next
    Next
    Transpose2DArray = arrResult
End Function

 

[drom]

Yes This is what I am doing at present but I do not want to create an array with the data if not necessary
So I thought the same way we can get the number os columns using:
objRs.Fields.Count 'which works
There was another way to do the same for rows, kind of:
objRs.Rows.Count 'which does not work

 

[drom]

Putting:
bjRs.CursorType = adOpenStatic 'Before Opening
We can get the number of rows
Debug.Print objRs.Fields.Count, objRs.recordCount 'Columns and Rows