VBA ADODB Record Count

[borgek]

Hi,
For some reason the record count for one of my sql-queries return -1. I have the exact same connection and query above, which returns the correct number of records. sqlRS.RecordCount returns the correct number of records, while sqlrs_c.RecordCount returns -1 (but ir really returns 5 to the worksheet).

Thanks,

Sub Demo1()
Dim sqlconn As New ADODB.Connection
Dim sqlRS As New ADODB.Recordset
Dim sqlRS_c As New ADODB.Recordset
Dim sqlRS_i As New ADODB.Recordset
Dim sqlstr As String


'checks to see if sheet "data" and "port" is created
Call check_sheet
Set wsd = ThisWorkbook.Sheets("data")
Set wsp = ThisWorkbook.Sheets("port")


sqlconn = "Provider=sqloledb; Data Source= BORGEK\SQLEXPRESS; Initial Catalog=nbim_dw; Integrated Security = SSPI;"


sqlconn.Open
'dump all data
sqlstr = ("select * from security_list")
Set sqlRS = Nothing
sqlRS.Open sqlstr, sqlconn, adOpenStatic, adLockOptimistic
wsd.Cells.Clear
wsd.Range("A2").CopyFromRecordset sqlRS
For icols = 0 To sqlRS.Fields.Count - 1
    wsd.Cells(1, icols + 1).Value = sqlRS.Fields(icols).Name
Next


'select distinct currencies
sqlstr_c = ("select distinct currency from security_list")
Set sqlRS_c = Nothing
sqlRS_c.Open sqlstr_c, sqlconn, adOpenStatic, adLockOptimistic
'wsd.Cells(1, icols + 4).Value #insert currency
wsd.Cells(2, icols + 4).CopyFromRecordset sqlRS_c


For icurr = 0 To sqlRS_c.RecordCount - 1
    wsp.Cells(2, icurr + 1).Value = wsd.Cells(icurr + 2, icols + 4).Value
Next



wsp.Cells(2, 2).Value = sqlRS_c.RecordCount
wsp.Cells(14, 14).Value = sqlRS_c.RecordCount




sqlRS_c.Close
sqlRS_i.Close
sqlRS.Close
sqlconn.Close


End Sub

 

[borgek]

I simplified the code some, so it is easier for you to read.

Sub test2()
Dim sqlconn As New ADODB.Connection
Dim sqlRS As New ADODB.Recordset
Dim sqlRS_c As New ADODB.Recordset
Dim sqlstr As String
Dim sqlstr_c As String


sqlconn = "Provider=sqloledb; Data Source= BORGEK\SQLEXPRESS; Initial Catalog=nbim_dw; Integrated Security = SSPI;"
sqlconn.Open
sqlstr = ("select * from security_list")
sqlRS.Open sqlstr, sqlconn, adOpenStatic, adLockOptimistic


sqlstr_c = ("select distinct currency from security_list")
sqlRS_c.Open sqlstr_c, sqlconn, adOpenStatic, adLockOptimistic
ThisWorkbook.Sheets("sheet3").Cells(2, 2).Value = sqlRS_c.RecordCount
ThisWorkbook.Sheets("sheet3").Cells(2, 3).Value = sqlRS.RecordCount
End Sub

 

[borgek]

Solved: Had to include 'sqlRS_c.CursorLocation = adUseClient'