VBA SQL Server Query Connecting but not Returning Records

user_1939638 0
Viewed 124

I have this VBA code that issues a SQL Server query after a successful connection but the query returns no records (e.g., -1)

Function invested_funds() As Integer
Dim c As ADODB.Connection
Dim rs As ADODB.Recordset
Dim connectionstring As String
Dim sql As String

connectionstring = "Provider=SQLOLEDB;Data Source=DESKTOP-2TTG3GQ\SQLEXPRESS;" & _
                   "Initial Catalog=DB;" & _
                   "Integrated Security=SSPI;"

Set c = New ADODB.Connection
Set rs = New ADODB.Recordset
c.Open connectionstring

sql = "select [DB].[dbo].[db].[CSRoot] " & _  
      "from [DB].[dbo].[db] " 

If c.State = adStateOpen Then
    Debug.Print ("Connected") 'This prints!
End If

Set rs = c.Execute(sql) 
Debug.Print (rs.RecordCount)
invested_funds = CInt(rs.RecordCount)

End Function

However, I know records exist and the exact same query in SSMS does indeed return records

select  [DB].[dbo].[db].[CSRoot]
from [DB].[dbo].[db]

Many records, in fact.

How can this be?

vba excel sql-server
1 Answers

You can get the number of records using the rs.getRows function.

Function invested_funds() As Integer
    Dim c As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim connectionstring As String
    Dim sql As String
    
    connectionstring = "Provider=SQLOLEDB;Data Source=DESKTOP-2TTG3GQ\SQLEXPRESS;" & _
                       "Initial Catalog=DB;" & _
                       "Integrated Security=SSPI;"
    
    Set c = New ADODB.Connection
    Set rs = New ADODB.Recordset
    c.Open connectionstring
    
    sql = "select [DB].[dbo].[db].[CSRoot] " & _
          "from [DB].[dbo].[db] "
    
    If c.State = adStateOpen Then
        Debug.Print ("Connected") 'This prints!
    End If
    
    Set rs = c.Execute(sql)
    
    Dim arr As Variant, n As Integer
    
    arr = rs.GetRows
    n = UBound(arr, 2) + 1
    'Debug.Print (rs.RecordCount)
    Debug.Print n
    invested_funds = n

End Function
user_81070920
Related